Help with VLookup

[TripletDad]

I need a little help with Vlookup. I'm trying to use it to populate inventory levels. The list I'm pulling data from doesn't always have all the models on it, therefore the list I'm populating gets several #N/A's....

I don't want to remove the formula from those spots, because we may get more inventory in later, however I also don't want to see all the #N/A values. I want those spots to be blank.

I created an "if" formula, but must have messed something up. Perhaps somebody can tell me where I went wrong???

(BTW: The Vlookup function works perfectly if I remove it from the "if" formula, so I don't think that is the problem. My guess is it's something with the # sign in the N/A response.)


=IF(VLOOKUP(B3,'9000'!$A:$B,2,FALSE)="#N/A"," ",VLOOKUP(B3,'9000'!$A:$B,2,FALSE))

 

[Armando Montes]

Try this:
=VLOOKUP(B3,'9000'!$A:$B,2,0)&""
or this:
=IF(COUNTIF('9000'!$A:$B,B3),VLOOKUP(B3,'9000'!$A:$B,2,0),"")

 

[T. Valko]

I need a little help with Vlookup. I'm trying to use it to populate inventory levels. The list I'm pulling data from doesn't always have all the models on it, therefore the list I'm populating gets several #N/A's....

I don't want to remove the formula from those spots, because we may get more inventory in later, however I also don't want to see all the #N/A values. I want those spots to be blank.

I created an "if" formula, but must have messed something up. Perhaps somebody can tell me where I went wrong???

(BTW: The Vlookup function works perfectly if I remove it from the "if" formula, so I don't think that is the problem. My guess is it's something with the # sign in the N/A response.)


=IF(VLOOKUP(B3,'9000'!$A:$B,2,FALSE)="#N/A"," ",VLOOKUP(B3,'9000'!$A:$B,2,FALSE))

Try one of these...

If you're using Excel 2007 or later:

=IFERROR(VLOOKUP(B3,'9000'!$A:$B,2,0),"")

That will trap ALL errors, not just #N/A errors.

This one will work in all versions of Excel:

=IF(COUNTIF('9000'!$A:$A,B3),VLOOKUP(B3,'9000'!$A:$B,2,0),"")

 

[TripletDad]

Thank you very much for all the help!

 

[T. Valko]

Thank you very much for all the help!

You're welcome. Thanks for the feedback!