Vlookup returning a blank cell

[dhally]

Hello;
I have viewed numerous threads regarding: Vlookup returning a blank cell if is lookup returns #NA.

Yet, when I use the the accepted formula format given on many different websites I get a return of a number zero when I really want a return of a blank cell. Here is my original Vlookup formula:
=VLOOKUP(A300,'VLOOKUP_FOR_BLANK_RANTALA_Request For SAP Upload Form.xlsx'!LookupContainer,12,FALSE)

Here is the formula I had hoped would return a blank cell:
=IF(ISNA(VLOOKUP(A301,'VLOOKUP CONTAINER'!K301:L301,2)),"",(VLOOKUP(A301,'VLOOKUP CONTAINER'!K301:L301,2))

Any help would be very appreciated. Thank you in advance
dh

 

[dhally]

I have a tremendous range where this formula is used. So I would have to create a macro?

 

[dhally]

AH....I'll send it to you right away. Thank you.

 

[Jonmo1]

OK, I see you don't actually have any 1 row arrays in your vlookup formulas.

That was apparently just a simplified example of the formula, where you removed the named range (lookupcontainer)


Also, when using named ranges, you don't need to specify the sheetname in your formulas because the sheetname is already specified in the named range.
So your formulas can be

=IFERROR(IF(VLOOKUP(A66,LookupContainer,10,0)="","",VLOOKUP(A66,LookupContainer,10,0)),"")



Now, this is a case where using INDEX/MATCH instead of vlookup can be very advantageous.
You have 22 columns with 235 Rows = 5170 Formulas
Doing the error checking and test for "", each formula does a match type function twice.

So in total, you're doing a match type function 5170 X 2 = 10340 times.

Using index/match, you can reduce that to 470 times, just twice for each row.


Are you looking to improve performance of your sheet?

 

[dhally]

That's a more simplified formula. The information regarding named ranges is a great help.

Now, as far as INDEX/MATCH go, I was actually looking at and trying to understand the difference between the two yesterday. Now that I'm getting more used to Vlookup and the power behind it, INDEX/MATCH will be a fun challenge.

Improvement's to the worksheet:

I'm not sure how one would go about improving beyond the simplified formula you provided. I think it's a terrific improvement from two days ago for sure, if you know what I mean.

Without having the incredible grasp (yet) of formulas, macro's etc., you kind people have, my boss may ask me to dig a round hole in the dirt, (build you a spreadsheet with certain required formulas). However, with my current limited knowledge (thank the Lord for this website), the hole will appear round, but instead of using a shovel (your level of excel understanding) I used a 2x4 to dig it with .

Good thing is...it's functional. I look forward to the day I can just pop in a module and be on my happy way. The fun is getting there!

Thank you for so much for everyone's care and considerations.
dh

 

[dhally]

OK, I see you don't actually have any 1 row arrays in your vlookup formulas.

That was apparently just a simplified example of the formula, where you removed the named range (lookupcontainer)


Also, when using named ranges, you don't need to specify the sheetname in your formulas because the sheetname is already specified in the named range.
So your formulas can be

=IFERROR(IF(VLOOKUP(A66,LookupContainer,10,0)="","",VLOOKUP(A66,LookupContainer,10,0)),"")



Now, this is a case where using INDEX/MATCH instead of vlookup can be very advantageous.
You have 22 columns with 235 Rows = 5170 Formulas
Doing the error checking and test for "", each formula does a match type function twice.

So in total, you're doing a match type function 5170 X 2 = 10340 times.

Using index/match, you can reduce that to 470 times, just twice for each row.


Are you looking to improve performance of your sheet?

Would this Index/Match be performed by a formula? If so, would it still return values similar to vlookup?

 

[Jonmo1]

Check you're email.
I sent the file back with improved INDEX/MATCH replacing the Vlookup.

Basically put
=IFERROR(MATCH(A66,INDEX(LookupContainer,0,1),0),"")
In an available columns (X)

Then used
=IF(INDEX(LookupContainer,X66,10)="","",INDEX(LookupContainer,X66,10))

Instead of Vlookup.


And since this is XL2007, and we have the IFERROR function, we only have to do the match ONCE per row.
So 235 match functions, instead of 10340

 

[dhally]

You will need to send the improved worksheet to the email I will send you via pm...when I figure out how to send you a pm

 

[Jonmo1]

You will need to send the improved worksheet to the email I will send you via pm...when I figure out how to send you a pm

You cen Send email from work, but not recieve?...hmm..

You can just reply to the PM I sent you, or just click on my name, that will bring up a menu with Send PM.