I need help trying to get a false return blank.

Severno

New Member
Joined
Feb 21, 2024
Messages
16
Office Version
  1. 365
Platform
  1. Windows
=INDEX('Master Store List_Final'!$C:$C,MATCH(Sheet1!O6,'Master Store List_Final'!$B:$B,0))

So I finally got this to work because I was so used to using VLOOKUP. The only problem is when the lookup is blank is comes back as N/A. I would like it to come back as as blank by using "". So i figured an IF statement would work but I am having trouble figuring out the IF statement if the above formula is a TRUE and the FALSE would read as ""
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Maybe wrapping your formula around the IFNA function as such:

Excel Formula:
=IFNA(INDEX('Master Store List_Final'!$C:$C,MATCH(Sheet1!O6,'Master Store List_Final'!$B:$B,0)),"")
 
Upvote 0
Another option
Excel Formula:
=XLOOKUP(Sheet1!O6,'Master Store List_Final'!B:B,'Master Store List_Final'!C:C,"")

BTW, what is the name of the sheet that this formula is on?
 
Upvote 0
Maybe wrapping your formula around the IFNA function as such:

Excel Formula:
=IFNA(INDEX('Master Store List_Final'!$C:$C,MATCH(Sheet1!O6,'Master Store List_Final'!$B:$B,0)),"")
Well, I wasn't even thinking of this and I was hopeful but it didn't work.
 
Upvote 0
Another option
Excel Formula:
=XLOOKUP(Sheet1!O6,'Master Store List_Final'!B:B,'Master Store List_Final'!C:C,"")

BTW, what is the name of the sheet that this formula is on?
I'm writing it on sheet2. I there are no official names yet I am just trying to get the formula to work first.
 
Upvote 0
I'm writing it on sheet2. I there are no official names yet I am just trying to get the formula to work first.
I'm going to try these at my house a little later. I always have a hard time on my work PC. Thank you and I will let you know if it works.
 
Upvote 0
I'm writing it on sheet2.
Thanks for that. I thought that it may have been on Sheet1 (used in the formula). If that was the case i was going to recommend a change to the formula.
 
Upvote 0
Thanks for that. I thought that it may have been on Sheet1 (used in the formula). If that was the case i was going to recommend a change to the formula.
So far both of these suggestions didn't work. I'm surprised because it's seems so simple and both seem like they would.
 
Upvote 0
So far both of these suggestions didn't work.
In that case perhaps we need some sample data (see XL2BB) to determine what is different about your data/setup to ours.
Here is mine. Do I have incorrect set-up and/or incorrect results?

Severno.xlsm
BC
1
2a22
3c33
4d44
5
Master Store List_Final


Severno.xlsm
O
6a
7b
8c
Sheet1


Severno.xlsm
B
122
2 
333
Sheet2
Cell Formulas
RangeFormula
B1:B3B1=XLOOKUP(Sheet1!O6,'Master Store List_Final'!B:B,'Master Store List_Final'!C:C,"")
 
Upvote 0
In that case perhaps we need some sample data (see XL2BB) to determine what is different about your data/setup to ours.
Here is mine. Do I have incorrect set-up and/or incorrect results?

Severno.xlsm
BC
1
2a22
3c33
4d44
5
Master Store List_Final


Severno.xlsm
O
6a
7b
8c
Sheet1


Severno.xlsm
B
122
2 
333
Sheet2
Cell Formulas
RangeFormula
B1:B3B1=XLOOKUP(Sheet1!O6,'Master Store List_Final'!B:B,'Master Store List_Final'!C:C,"")
Thats is what I am looking for. We are to enter the number of a store in sheet 1 and on sheet 2 it will spit out the Name of the store. Some of these stores have zeros in the front so I formatted all cells to text. I am wondering if there is something else I can change because the formula looks right. The original formula I put up does work it just spits out those **** N/A lol
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,155
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top