Lookup based on 2 criteria

[L. S. Martin]

I need to perform a lookup based on information shown in 2 adjacent columns
(Col. A = Date, Col. B = UserID). Say the column with the information is in
column C. Have no problem looking up based on just Column A, using
VLOOKUP(look_upvalue, table_array,MATCH(lookup_value, lookup_array,
match_type), FALSE), but it stops on the first entry for the date. I need
to look up info. for a certain user ID on a given date. After looking in
Help, it looks like INDEX might work, but I don't know how to set it up.
Any help would be greatly appreciated.
Thanks,
LSM

 

[Guest]

I've concatenated two cells together and have done a VLOOKUP on the
concatenated cells.

 

[Andy Wiggins]

This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/vlookup2.zip
It's in the "Worksheet" section on page:
http://www.bygsoftware.com/examples/examples.htm

Look up on two fields with this alternative to VLOOKUP

 

[L. S. Martin]

Thanks Barb. I hadn't thought of trying that. I guess you concatenated the
information in the two columns in the lookup range and the two entries
you're trying to lookup?

 

[L. S. Martin]

Thanks - sounds like EXACTLY what I was looking for!

 

[kk]

I'm using the following array formula. You may want to try...

A1:A10 - Date
B1:B10 - User ID
C1:C10 - Information

In E1: Date to lookup
In F1: User ID to lookup

In G1
=INDEX($C$1:$C$10,MATCH(1,($A$1:$A$10=$E1)*($B$1:$B$10=$F1),0))

Confirm it by hitting Ctrl + Shift + Enter

kk

I need to perform a lookup based on information shown in 2 adjacent columns
(Col. A = Date, Col. B = UserID). Say the column with the information is in
column C. Have no problem looking up based on just Column A, using
VLOOKUP(look_upvalue, table_array,MATCH(lookup_value, lookup_array,
match_type), FALSE), but it stops on the first entry for the date. I need
to look up info. for a certain user ID on a given date. After looking in
Help, it looks like INDEX might work, but I don't know how to set it up.
Any help would be greatly appreciated.
Thanks,
LSM

 

[kk]

I'm using the following array formula. You may want to try...

A1:A10 - Date
B1:B10 - User ID
C1:C10 - Information

In E1: Date to lookup
In F1: User ID to lookup

In G1
=INDEX($C$1:$C$10,MATCH(1,($A$1:$A$10=$E1)*($B$1:$B$10=$F1),0))

Confirm it by hitting Ctrl + Shift + Enter

kk

I need to perform a lookup based on information shown in 2 adjacent columns
(Col. A = Date, Col. B = UserID). Say the column with the information is in
column C. Have no problem looking up based on just Column A, using
VLOOKUP(look_upvalue, table_array,MATCH(lookup_value, lookup_array,
match_type), FALSE), but it stops on the first entry for the date. I need
to look up info. for a certain user ID on a given date. After looking in
Help, it looks like INDEX might work, but I don't know how to set it up.
Any help would be greatly appreciated.
Thanks,
LSM

 

[L. S. Martin]

I can't seem to get the function to work for me. I went to VBA Editor,
created a Module and copied VLookup2 into it, then returned to the worksheet
and chose Function and filled in the blanks, but got N/A (checked to make
sure right - was). The data I'm looking up is on a different worksheet -
does that make any difference?

 

[L. S. Martin]

Thanks for the info. I don't understand the "1" after the MATCH, but tried
it anyway and several variations. I never could get it to work. I believe
the problem is that the information I'm looking up is on a different sheet
from the two lookup entries (though that's no problem when looking up just
one entry). Thanks for trying.

 

[kk]

It work for me. I can send you the sample workbook if you need.

Thanks for the info. I don't understand the "1" after the MATCH, but tried
it anyway and several variations. I never could get it to work. I believe
the problem is that the information I'm looking up is on a different sheet
from the two lookup entries (though that's no problem when looking up just
one entry). Thanks for trying.

 

[malik641]

This works:

E1=Date
F1=UserID

{=INDEX($C$1:$C$10,MATCH($E$1,$A$1:$A$10,0),MATCH($F$1,$B$1:$B$10,0))}

Array function, so press Ctrl+Shft+Enter

 

[LM]

Malik,
Thanks for the information. I'm afraid I still can't get it or any of
the other suggestions to work. I'm running Excel 2000 and trying create a
worksheet that will get information from several other tabs in the same
workbook. Each Tab has been sorted and has the dates in Column A and the
users in Column B. On this worksheet I am entering the date in Cell A3
(merged a,b,c) and the User in Cell D3, then in other cells I am entering
the Index/Match formulas to look up say the Hi Temp in Column G of the Tab
named DWRs. I will send an abbreviated copy of the workbook if anyone is
willing to take a look at it.
Thanks also to KK for offering a copy of his workbook.

 

[malik641]

Yes I will take a look at it if you still need it....sorry I haven't
responded to this in a while...send me a PM if you can...if not, just
post back and I'll give you my e-mail

 

[rviswesw]

Hi Friends,

I am new to excel coding. I am trying to use vlookup formula to get
data from other file which is residing in my EXCEL default directory.
Please see the Example..

I have File name called File_A which is resides in my default directory
(C:\Excel\)
I have File name called File_B and writing a vlookup formula in this
file.. the formula is like this..

=Vlookup(A1,File_A.xls!Rangename,2).

But As soon as I come out of this cell, the formula is getting change
to
=Vlookup(A1,C:\EXCEL\File_A.xls!Rangename,2).

What I want here is, I do not want to specify the path (C:\Excel\). The
formula should automatically recoganice the default path and look for
File_A file and retrive a data.. Could any one respond to my mail
please..

my email id is (e-mail address removed)..

Thanks in advance
Vish