VLOOKUP CONDITION TO RETURN BLANK CELL WHERE LOOKUP VALUE IS NOT IN TABLE ARRAY

[Scott Lolmaugh]

Greetings all,

I have a weekly table of part numbers and inventory quantities I'm tracking
for a certain area.
I also get a table showing the net changes from the previous week in this
same area.
Not every PN in my inventory list will have changed that week.
My lookup table contains only those PNs that have changed in the last week
so it is only a subset of the main inventory list.

I created a VLOOKUP formula for every row of my inventory list that will
look in the change table for any changes and return that value to the cell.

=VLOOKUP(B2,$H$4:$I$18,2,FALSE)

Right now when the lookup value (PN) doesn't exist in the table the cell
returns "#N/A".
I want to qualify my formula so that if the exact lookup value (PN) is not
in the lookup table, it will return a "blank" cell.
I tried using an IF statement such as:

=IF(VLOOKUP(B2,$H$4:$I$18,2,FALSE)="N/A","",VLOOKUP(B2,$H$4:$I$18,2,FALSE))

....but obviously the text "N/A" is not the value of the cell for conditional
testing. I found that the ERROR.TYPE() for "N/A" = 7,
so then I tried...

=IF(ERROR.TYPE(VLOOKUP(B2,$H$4:$I$18,2,FALSE))=7,"",VLOOKUP(B2,$H$4:$I$18,2,FALSE))

and this gives me the blank cell I wanted, but for those rows where there IS
a value in the lookup table it now returns "#N/A" instead of the value.

What do I need to do?

Thanks for your help,
Scott

 

[L. Howard Kittle]

Hi Scott,

You were pretty close, try this.

=IF(ISNA(VLOOKUP(B2,$H$4:$I$18,2,FALSE)),"",VLOOKUP(B2,$H$4:$I$18,2,FALSE))

HTH
Regards,
Howard

 

[Richard Buttrey]

Greetings all,

I have a weekly table of part numbers and inventory quantities I'm tracking
for a certain area.
I also get a table showing the net changes from the previous week in this
same area.
Not every PN in my inventory list will have changed that week.
My lookup table contains only those PNs that have changed in the last week
so it is only a subset of the main inventory list.

I created a VLOOKUP formula for every row of my inventory list that will
look in the change table for any changes and return that value to the cell.

=VLOOKUP(B2,$H$4:$I$18,2,FALSE)

Right now when the lookup value (PN) doesn't exist in the table the cell
returns "#N/A".
I want to qualify my formula so that if the exact lookup value (PN) is not
in the lookup table, it will return a "blank" cell.
I tried using an IF statement such as:

=IF(VLOOKUP(B2,$H$4:$I$18,2,FALSE)="N/A","",VLOOKUP(B2,$H$4:$I$18,2,FALSE))

...but obviously the text "N/A" is not the value of the cell for conditional
testing. I found that the ERROR.TYPE() for "N/A" = 7,
so then I tried...

=IF(ERROR.TYPE(VLOOKUP(B2,$H$4:$I$18,2,FALSE))=7,"",VLOOKUP(B2,$H$4:$I$18,2,FALSE))

and this gives me the blank cell I wanted, but for those rows where there IS
a value in the lookup table it now returns "#N/A" instead of the value.

What do I need to do?

Thanks for your help,
Scott

You need to wrap your vlookup inside an IF and ISNA() function

i.e.

=IF(ISNA(VLOOKUP(B2,$H$4:$I$18,2,FALSE)),"",VLOOKUP(B2,$H$4:$I$18,2,FALSE))

HTH


Richard Buttrey
__

 

[Biff]

Another way:

=IF(COUNTIF($H$4:$H$18,B2),VLOOKUP(B2,$H$4:$I$18,2,0),"")

Biff