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" <[email protected]> wrote in message
news:[email protected]...
> 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