2 Column Data lookup

[Hari]

Hi,

I have data in column A, B and C starting from row number 2.

Also, I have some data in column P and Q starting from row number 2.

The data in A and B TOGETHER constitute a Unique ID. Similarly data in
P and Q together constitute a Unique ID.

Now based on the data in P and Q I want to look up A and B and return
value from C in column R.

As of now, I use an inefficient way of building up a helper column in D
(D2 = A2 & B2) and S (S2 = P2 and Q2). Then in R2 I write the formula,
= =INDEX($C$2:$D$1000,MATCH(S2,$D$2:$D$1000,0),1)

Is there a better way of accomplishing the above (an array formula or
some mega formula which doesnt resort to inserting dummy columns etc)

Please guide me.

Regards,
Hari
India

 

[Biff]

Hi!

Try this:

Array entered:

=INDEX($C$2:$D$1000,MATCH(P2&Q2,$A$2:$A$1000&$B$2:$B$1000,0),1)

Is it more efficient than using 2 helper columns? Maybe, maybe not! It
depends!

Biff

 

[Hari]

Hi Biff,

Thnx for the formula. Getting the idea.

Regards,
Hari
India