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
Chris Marlow - 22 Feb 2006 17:56 GMT
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.

Signature
Chris Marlow
MCSD.NET, Microsoft Office XP Master
> Hi,
>
[quoted text clipped - 27 lines]
>
> Maileen
Bob Phillips - 22 Feb 2006 17:58 GMT
=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.

Signature
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
> Hi,
>
[quoted text clipped - 27 lines]
>
> Maileen
Dave Peterson - 22 Feb 2006 18:08 GMT
You have a reply in .misc.
> Hi,
>
[quoted text clipped - 27 lines]
>
> Maileen

Signature
Dave Peterson
Dave Peterson - 22 Feb 2006 18:14 GMT
.excel. Sorry.
> You have a reply in .misc.
Ardus Petus - 22 Feb 2006 18:08 GMT
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
> Hi,
>
[quoted text clipped - 27 lines]
>
> Maileen