multiple lookups - xls2003

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am close to getting this formula correct but can't seem to get it just right.

sheet1 I have:
Customer Name Line 1 Address City State Zip
ACADIA PHARMACEUTICALS SAN DIEGO CA 92121
ALEXION ANTIBODY SAN DIEGO CA 92121
UNIVERSITY OF SOUTH MOBILE AL 36688
UNIVERSITY OF SOUTH MOBILE AL 36688

sheet2 I have a defined name for my data called "products" This sheet
contains data such as:
Zip Name City
State Rep
92121 ACADIA PHARMACEUTICALS SAN DIEGO CA RHENSLEYPA
92121 ACADIA PHARMACEUTICALS SAN DIEGO CA RHENSLEYPA
92121 ADVENTRX PHARMACEUTICALS SAN DIEGO CA RCATONMA

Even though the zip is the same in sheet2 I need to show the different Rep
name based on the different customer name.

I have my formula like this: =INDEX(D3,MATCH(1,(products)*(A3)*(products),0))

What am I doing wrong?

Thank you in advance!
Rogers
 
In Sheet1
----------
Try, array-entered (press CTRL+SHIFT+ENTER) in say, F2:

=INDEX(Sheet2!$E$2:$E$100,MATCH(TRIM(D2)&"_"&TRIM(A2),Sheet2!$A$2:$A$100&"_"
&Sheet2!$B$2:$B$100,0))

Copy F2 down

Above assumes Cust name and zip are in cols A and D in Sheet1,
while Cust name, zip and Rep names are in cols A, D and E in Sheet2 (all
data taken to be from row2 down)

Adapt the ranges to suit ..

And perhaps better with an error trap to return blanks instead of #NAs, we
could put instead in F2:

=IF(ISNA(MATCH(TRIM(D3)&"_"&TRIM(A3),Sheet2!$A$2:$A$100&"_"&Sheet2!$B$2:$B$1
00,0)),"",INDEX(Sheet2!$E$2:$E$100,MATCH(TRIM(D3)&"_"&TRIM(A3),Sheet2!$A$2:$
A$100&"_"&Sheet2!$B$2:$B$100,0)))

and copy F2 down, as before
 
Clarification: The formulas in col F in Sheet1 will return the Rep names
from Sheet2, matched using a combination of Cust name & Zip (as match on Zip
alone is not sufficient - not unique). Unmatched cases will return #NAs or
blanks (with the error trap).
 
Back
Top