2 way Vlookup - Creating array arguments from columns

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

See response in .excel

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

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