MrExcel Publishing
Your One Stop for Excel Tips & Solutions

lookup question


Posted by Anna on August 29, 2001 6:36 AM

Dear Mr Excel,

If I have a Table (A) which contains two sets of values, Code and Class, and in Table (B) I have a list of code values. What is the function in Excel that will help me to "Lookup" the Code value from table (A), compares with the Code value from table (B) and if there are the same, return the value -class next to the code in table (b)?

Table A
Class Natural
a 5025
a 5030
a 5305
a 6416
a 7606
b 4030
b 4032
b 4034
b 4040
b 4041
b 4042
b 4044
b 4045


Table B
Code Class
4001 (lookup Table A - B2:B124{Code}, if value = A2, return the value "Class" in table A)
4010
4032
4034
4040
4041
4042
4043
4044
4045
4046
4100
Appreciate you advise on this.

Thanks in advance
Anna


Posted by Aladin Akyurek on August 29, 2001 6:53 AM

Supposing that you have table A in columns A and B

and table B in D and E (all starting in row 2),

in E2 enter: =IF(ISNUMBER(MATCH(D2,$B$2:$B$100,0)),INDEX($A$2:$A$100,MATCH(D2,$B$2:$B$100,0)),"")

Copy down this as far as needed.

Adjust for ",". :)

Aladin