vlookup macro

[Guest]

I have a spreadsheet that looks like data below.

UPC Commission
1234 5%
4567 Not On file
8999 3%

What I need to do is do a lookup by UPC to another spreadsheet and return
the commission %. I'm only interested in doing this for the rows in which
the Commission field says "Not On file".

The spreadsheet that I am looking up the data from is called
"Commission.xls" and has the UPC in column a and the Commission in column B.
Each time I try to right a formula to do the lookup I get an error.

Any help would be great. Thanks!

 

[Guest]

Start there on the row that has equivalent of the 4567 Not On File entries
and create a VLOOKUP that actually works. It might look something like this
(presumes we're in row 3) :
=VLOOKUP(A3,'[Commission.xls]TheSheetInOtherBook'!$A$1:$B$101,2,0)
which says to try to match the value in A3 on this sheet with an entry in
column B on the other book's sheet and if it finds a match, return the value
in the 2nd column of that table (column B). If it doesn't find a match it
will return a #N/A error - that is normal.

To hide the #N/A from showing up, you wrap that in a test with IF, as:
=IF(ISNA(VLOOKUP(A3,'[Commission.xls]TheSheetInOtherBook'!$A$1:$B$101,2,0),"",VLOOKUP(A3,'[Commission.xls]TheSheetInOtherBook'!$A$1:$B$101,2,0))

Finally, since you don't want to always do this except when column B
contains the key phrase, use one more IF layer:
=IF(B3="Not On
File,IF(ISNA(VLOOKUP(A3,'[Commission.xls]TheSheetInOtherBook'!$A$1:$B$101,2,0),"",VLOOKUP(A3,'[Commission.xls]TheSheetInOtherBook'!$A$1:$B$101,2,0)),"")