Vlookup

KVR1980

New Member
Joined
Mar 16, 2021
Messages
10
Office Version
  1. 2010
Platform
  1. Windows
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))
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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.
 
Upvote 0
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:

Excel Formula:
=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),""))
 
Upvote 0
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:
Excel Formula:
=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)),""))
 
Upvote 0
Solution
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.
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,819
Messages
6,181,153
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