lookup function with 2 parameter in VBA ?

[Maileen]

Hi,

on sheet2, i have the following table
col1 col2 col3
A 1 10
A 2 11
B 1 20
B 2 25
C 1 30
C 2 32
....

on sheet1, i have the following table

col1 col2 col3
A 2
B 1
A 1
C 1

i would like in col3 the result of the search...something like
research on sheet 2 the couple sheet1:col1 and sheet1:col2
if found sheet1:col3 = result of research

this exist vis LOOKUP function, but it does not allow a research based on 2
parameter...
how can i do it ?

thanks a lot for help

Maileen

 

[Guest]

Maileen,

The simplest way I know is to insert another column on the table you are
looking up into (to the left of the column with the value you are looking up)
that concatenates col1 & 2 and perform the vlookup against this column
(concatenating the 2 cells that make up the key in the vlookup function
itself).

You can do some more funky stuff with SUMPRODUCT & array formulae, but the
above suffices for me most of the time.

Regards,

Chris.

 

[Bob Phillips]

=INDEX(Sheet1!C1:C100,MATCH((Sheet1!A1:A100=A1)*(Sheet1!B1:B100=B1),Sheet1!C
1:C100,0)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Dave Peterson]

You have a reply in .misc.

Hi,

on sheet2, i have the following table
col1 col2 col3
A 1 10
A 2 11
B 1 20
B 2 25
C 1 30
C 2 32
...

on sheet1, i have the following table

col1 col2 col3
A 2
B 1
A 1
C 1

i would like in col3 the result of the search...something like
research on sheet 2 the couple sheet1:col1 and sheet1:col2
if found sheet1:col3 = result of research

this exist vis LOOKUP function, but it does not allow a research based on 2
parameter...
how can i do it ?

thanks a lot for help

Maileen

 

[Ardus Petus]

IMHO, the best way would be to create "hidden" columns on each sheet, with a
formula like:
=A1&"|"&B1

Then yo can use LOOKUP function based on those combo columns.

HTH,

 

[Dave Peterson]

..excel. Sorry.