2 way Vlookup - Creating array arguments as needed

[Hari]

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.

[Bob Phillips]

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

[Ardus Petus]

Try:
=SUMPRODUCT((G2=A2:A500)*(H2=B2:B500);D2:D500)
which you can copy down

HTH
--
AP

"Hari" <[email protected]> a écrit dans le message de news:
[email protected]...
> 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.
>

[Hari]

Bob,

Thanks a TON for the solution.

It works really well. I just added the column agument 1 for index
function (=INDEX(D1:D4,MATCH(1,((A1:A4=F1)*(B1:B4=G1)),0),1))

I think I had to log back to google and got driven to .excel group,
though I prefer to post using the .misc group.

regards,
HP
India

[Bob Phillips]

"Hari" <[email protected]> wrote in message
news:[email protected]...
> Bob,
>
> Thanks a TON for the solution.
>
> It works really well. I just added the column agument 1 for index
> function (=INDEX(D1:D4,MATCH(1,((A1:A4=F1)*(B1:B4=G1)),0),1))

That shouldn't be necessary. Did you have a problem without it?

[Hari]

Bob,

Thanks. Nice stuff to learn for the day. I never noticed that the
column argument for Index is optional (when row number is already
menioned and viceversa).

regards,
HP
India

[Hari]

Ardus,

I tried with

=SUMPRODUCT((A1:A4=F1)*(B1:B4=G1),D1:D4)

but it returns me zero which is wrong. (Note, putting semicolon between
B1:B4=G1) and D1:D4 is not a valid formula syntax).

Regards,
HP
India

[Hari]

Hi,

This is in continuation to Ardus's solution to my post "2 way Vlookup -
Creating array arguments as needed"

http://groups.google.co.in/group/mic...336107f3?hl=en

I would like to know as to whether it is possible to perform lookup (of
a string value) kind of operation with Sumproduct function or not.
Presently am using Shift+Ctrl+Enter along with Index but would be happy
to learn other ways as well.

Regards,
HP
India


Hari wrote:
> Ardus,
>
> I tried with
>
> =SUMPRODUCT((A1:A4=F1)*(B1:B4=G1),D1:D4)
>
> but it returns me zero which is wrong. (Note, putting semicolon between
> B1:B4=G1) and D1:D4 is not a valid formula syntax).
>
> Regards,
> HP
> India