2 way Vlookup - Creating array arguments as needed

H

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.
 
B

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)
 
H

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
 
B

Bob Phillips

Hari said:
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?
 
H

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
 
H

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with a function 2
Array Formula 13
Need to stop VLOOKUP formula changes 2
VLOOKUP Headache 3
Vlookup problem 7
Vlookup 5
Array lookup 1
Lookup help 6

Top