Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
vlookup macro
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! |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
vlookup macro
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(A 3,'[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(A 3,'[Commission.xls]TheSheetInOtherBook'!$A$1:$B$101,2,0)),"") "MikeD1224" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Specifying cell(s) for Vlookup Function in Macro | Excel Discussion (Misc queries) | |||
Replace Vlookup with a macro | Excel Discussion (Misc queries) | |||
problem with Vlookup in macro | Excel Discussion (Misc queries) | |||
VLOOKUP in a macro?? | Excel Discussion (Misc queries) | |||
Macro puts #REF in VLOOKUP formula | Excel Worksheet Functions |