Index…Match (Excel 2003)

Topic

New Reply

Hi

Given the following data in sheet1 and using

=INDEX(Sheet1!$C$2:$C$100,INDEX(MATCH(B2&C2,Sheet1!$A$2:$A$100&Sheet1!$C$2:$C$100,0),0))
I am unable to get the expected result as in sheet 2

First Name ………………… Last Name……………….Classification
Bloggs………………………..Joe…………………………….Management
Jones………………………….Fred…………………………..Engineer

In sheet2, I get after placing the above formula in C2 :

First Name…..Last Name……..Classification
Bloggs……..Joe……………………0
Jones……….Fred………………….0

I should get the Classification right as above but I don’t. I beleive that I have missed the some parameters in the formula but unable to find it.

TIA, fy

Reply | Quote

Replies

If both sheet 1 and Sheet2 are the same with first name in A nd last name in B, then in C2 of Sheet2 I think you need:

=INDEX(Sheet1!$C$2:$C$100,INDEX(MATCH(A2&B2,Sheet1!$A$2:$A$100&Sheet1!$B$2:$B$100,0),0))

As you have it written it contains a circular reference (C2 refers to C2)

Steve

Reply | Quote

Thanks, Steve. Got it.

Reply | Quote