2 way Vlookup - Creating array arguments from columns

[Hari]

(Pardon me in case this post appears muliple times. Google gives me
PCBD error on submitting the post)

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),(C2500)),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.

 

[Bob Phillips]

See response in .excel

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)