Vlookup Help

**aharvestofhealth** · 02-18-2010, 01:42 PM

I am trying to create a vlookup without success. I would like cell D2 (on Sheet1) to default to the value of "EACH", unless the value is found on Sheet2 (column C). If it is found on Sheet2, then I want it to match the unit beside the respective upc code (see Col Q on Sheet1, and Col A on Sheet2) . Then I would like to be able to drag down cell D2 and all cells on Sheet2 would match and any that do not match, would be filled with "Each". Desired results are shown in attached example file.

**NBVC** · 02-18-2010, 01:49 PM

Remove Data Validation and try:

=IF(ISNUMBER(MATCH(Q2,Sheet2!A:A,0)),VLOOKUP(Q2,Sheet2!A:C,3,0),"EACH")

copied down

**aharvestofhealth** · 02-18-2010, 02:13 PM

Originally Posted by NBVC

Remove Data Validation and try:

=IF(ISNUMBER(MATCH(Q2,Sheet2!A:A,0)),VLOOKUP(Q2,Sheet2!A:C,3,0),"EACH")

copied down

Beautiful! You guys are amazing!!!

**aharvestofhealth** · 02-18-2010, 02:39 PM

One last question. If I have a upc on sheet2 without a unit, it returns a 0. How do I get it to just show a blank cell?

**NBVC** · 02-18-2010, 02:55 PM

Try:

=IF(ISNUMBER(MATCH(Q2,Sheet2!A:A,0)),IF(VLOOKUP(Q2,Sheet2!A:C,3,0)=0,"",VLOOKUP(Q2,Sheet2!A:C,3,0)), "EACH")

You can also custom format the cells, using my first formula, as

;;;@