lookup function with 2 parameter in VBA ?
 

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

lookup function with 2 parameter in VBA ?
 

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.
--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"Maileen" wrote:

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

lookup function with 2 parameter in VBA ?
 

=INDEX(Sheet1!C1:C100,MATCH((Sheet1!A1:A100=A1)*(S heet1!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)

"Maileen" wrote in message
...
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

lookup function with 2 parameter in VBA ?
 

You have a reply in .misc.

Maileen wrote:

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

--

Dave Peterson

lookup function with 2 parameter in VBA ?
 

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,
--
AP

"Maileen" a écrit dans le message de
...
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

lookup function with 2 parameter in VBA ?
 

..excel. Sorry.

Dave Peterson wrote:

You have a reply in .misc.

Maileen wrote: