Vlookup

[KVR1980]

I've written a nested Vlookup formula that looks across two worksheets (see example below). What I need it to do though is, if the two returning cells are empty then I want the cell to show as blank and not a zero. I know you can use an IF statement to achieve this (see 2nd example below), but I cant seem to combine the IF formula with the IFERROR formula that I need to look across the two worksheets.
Please can someone help me?

=IFERROR(VLOOKUP(A1,Sheet2!A:B,2,0),IFERROR(VLOOKUP(A1,Sheet3!A:B,2,0),""))

=IF(VLOOKUP(A1,Sheet2!A:B,2,0)="","",VLOOKUP(A1,Sheet2!A:B,2,0))

 

[Joe4]

Borrowing from this thread: Best way for VLOOKUP to deal with blank cells
maybe try this:

=IFERROR(VLOOKUP(A1,Sheet2!A:B,2,0)&"",IFERROR(VLOOKUP(A1,Sheet3!A:B,2,0)&"",""))

 

[Sufiyan97]

Hi Joe

What is the reason for using &""

 

[Joe4]

Hi Joe

What is the reason for using &""

The issue is that they are finding a match, but the column that they are returning from has nothing in it (is blank).
In those situations, a normal VLOOKUP returns a 0 instead of a blank. Adding the "" to the end of it coerces it to text and return an empty string instead of a 0, like they want.

 

[hagia_sofia]

I am not sure how your lookup is constructed, i.e. what should be returned when there are values in both sheets? Therefore, this might not be suitable for you:

=IF(ISBLANK(VLOOKUP(A1,Sheet2!A:B,2,0))=FALSE,VLOOKUP(A1,Sheet2!A:B,2,0),IF(ISBLANK(VLOOKUP(A1,Sheet3!A:B,2,0))=FALSE,VLOOKUP(A1,Sheet3!A:B,2,0),""))

 

[Alex Blakenburg]

We need to know whether you are expecting the return value to be a number or text.
If it is Text then you can use the &"" method suggested by @Joe4.
If you are expecting to return a number then that method will convert the numbers that get returned to text.

So if you are expecting numbers unless you have MS365 or O2021 so that you can use the LET function, you will need to do it the long way:

=IFERROR(IF(VLOOKUP(A1,Sheet2!A:B,2,0)="","",VLOOKUP(A1,Sheet2!A:B,2,0)),IFERROR(IF(VLOOKUP(A1,Sheet3!A:B,2,0)="","",VLOOKUP(A1,Sheet3!A:B,2,0)),""))

 

[Joe4]

Note that another possible option may be to simply use your original formula, and use Custom Formatting or Conditional Formatting to hide the 0 values.
Of course, that only works as long as there are not any zeroes values that you want to see.

 

[KVR1980]

Thanks for all the suggestions, the data being returned if its populated will all be dates, so which do you think the best option to use?

 

[KVR1980]

Note that another possible option may be to simply use your original formula, and use Custom Formatting or Conditional Formatting to hide the 0 values.
Of course, that only works as long as there are not any zeroes values that you want to see.

Thanks for all the suggestions, the data being returned if its populated will all be dates, so which do you think the best option to use?

 

[Alex Blakenburg]

Try post #6. Dates are effectively numbers so adding &"" won't work.
PS: Tell us and update your profile if you have upgraded from Excel 2010 though.