2 Column Data lookup
 

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

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" wrote in message
oups.com...
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

Hi Biff,

Thnx for the formula. Getting the idea.

Regards,
Hari
India