ExcelBanter - Lookup based on 2 criteria

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Lookup based on 2 criteria (https://www.excelbanter.com/excel-worksheet-functions/33843-lookup-based-2-criteria.html)

L. S. Martin

Lookup based on 2 criteria

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

Barb R.

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

"L. S. Martin" wrote:

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

Andy Wiggins

This file might be a help:
http://www.bygsoftware.com/examples/...s/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

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"L. S. Martin" wrote in message
...
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

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?
"Barb R." wrote in message
...
I've concatenated two cells together and have done a VLOOKUP on the
concatenated cells.

"L. S. Martin" wrote:

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

Thanks - sounds like EXACTLY what I was looking for!
"Andy Wiggins" wrote in message
...
This file might be a help:
http://www.bygsoftware.com/examples/...s/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

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"L. S. Martin" wrote in message
...
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

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

"L. S. Martin" wrote in message
...
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" wrote in message
...
Thanks - sounds like EXACTLY what I was looking for!
"Andy Wiggins" wrote in message
...
This file might be a help:
http://www.bygsoftware.com/examples/...s/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

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"L. S. Martin" wrote in message
...
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

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" <kkchoh @ yahoo dot com wrote in message
...
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

"L. S. Martin" wrote in message
...
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

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

"L. S. Martin" wrote in message
...
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" <kkchoh @ yahoo dot com wrote in message
...
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

"L. S. Martin" wrote in message
...
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

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

L. S. Martin Wrote:
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

--
malik641
------------------------------------------------------------------------
malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190
View this thread: http://www.excelforum.com/showthread...hreadid=384559

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

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

L. S. Martin Wrote:
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

--
malik641
------------------------------------------------------------------------
malik641's Profile:
http://www.excelforum.com/member.php...o&userid=24190
View this thread: http://www.excelforum.com/showthread...hreadid=384559

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
LM Wrote:
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" wrote
in
message ...

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

L. S. Martin Wrote:
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

--
malik641

------------------------------------------------------------------------
malik641's Profile:
http://www.excelforum.com/member.php...o&userid=24190
View this thread:
http://www.excelforum.com/showthread...hreadid=384559

--
malik641
------------------------------------------------------------------------
malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190
View this thread: http://www.excelforum.com/showthread...hreadid=384559

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 ..

Thanks in advance
Vish

--
rviswesw
------------------------------------------------------------------------
rviswesw's Profile: http://www.excelforum.com/member.php...o&userid=25289
View this thread: http://www.excelforum.com/showthread...hreadid=384559

All times are GMT +1. The time now is 06:41 AM.