=INDEX(D2:D500,MATCH(1,(A2:A500=G2)*(B2:B500=H2),0))

which is an array formula, so commit with Ctrl-Shift-Enter, not just Enter.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Hari" <excel_hari@yahoo.com> wrote in message
news:1147349293.856453.249620@y43g2000cwc.googlegroups.com...
> Hi,
>
> I have base data in column A, B, C and D extending from row 2 to row
> 500. The data in coln A and B together constitute an unique ID.
>
> I also have data in Column G and H and these 2 together correspond to
> the I formed from Column A and B. I want a formula which can return the
> value from Column D for a particulr ID combination in Column G plus H.
>
> Ideally I would concatenate the data in A and B ( = A2&B2) and use this
> ID for lookup with the concatenated data from G and H (=g2&h2). BUT, in
> this case due to the naure of the data layout (and some other factors)
> I cannot afford to create new columns for concatenation.
>
> Hence, I want to know as to how I can perform a 2 way lookup without
> doing any VBA coding.
>
> I thought that if I can create an array "on the fly" and pass as
> arguments which could form a table_array then my problem could be
> solved. Something like
>
> (probably this needs to be entered as an array formula)
>
> J2 = Vlookup(G2&H2, union((A2:A500)&(B2:B500),(C2:D500)),3,false)
>
> I know that the above formula is invalid and Union function exists only
> in VBA, but I wrote this to just explain as to what I want to do.
>
> Please guide me.
>
> Regards,
> Hari
> India
>
> PS : I know some amount of VBA and can do the above using it, but I
> just want to know if excel function magic can solve this.
>