Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / February 2006

Tip: Looking for answers? Try searching our database.

lookup function with 2 parameter in VBA ?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Maileen - 22 Feb 2006 17:36 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.