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

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

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

"Scott Lolmaugh" <[email protected]> wrote in message
news:%[email protected]...
> 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
>

On Thu, 9 Mar 2006 14:30:38 -0800, "Scott Lolmaugh" <[email protected]>
wrote:

>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
__

Another way:

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

Biff

"Scott Lolmaugh" <[email protected]> wrote in message
news:%[email protected]...
> 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
>