Lookup based on 2 criteria

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
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
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> 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
>
>
>
>

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
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> 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
>
>
>
>

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." <[email protected]> wrote in message
news:[email protected]...
> 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
> >
> >
> >

Thanks - sounds like EXACTLY what I was looking for!
"Andy Wiggins" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 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" <[email protected]> wrote in message
news:%[email protected]...
> Thanks - sounds like EXACTLY what I was looking for!
> "Andy Wiggins" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
news:[email protected]...
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" <[email protected]> wrote in message
news:[email protected]...
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" <[email protected]> wrote in message
news:[email protected]...
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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

"L. S. Martin" <[email protected]> wrote in message
news:[email protected]...
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
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> 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
>
>
>
>

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
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> 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 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" <[email protected]> wrote in message
news:%[email protected]...
> Thanks - sounds like EXACTLY what I was looking for!
> "Andy Wiggins" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
news:[email protected]...
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

Thanks - sounds like EXACTLY what I was looking for!
"Andy Wiggins" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
>
>

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." <[email protected]> wrote in message
news:[email protected]...
> 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
> >
> >
> >

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" <[email protected]> wrote in message
news:[email protected]...
> 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'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
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
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 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" <[email protected]> wrote in message
news:[email protected]...
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 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" <[email protected]> wrote in message
news:%[email protected]...
> Thanks - sounds like EXACTLY what I was looking for!
> "Andy Wiggins" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> > >
> >
> >
>
>

Thanks - sounds like EXACTLY what I was looking for!
"Andy Wiggins" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
>
>

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
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> 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
>
>
>
>

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." <[email protected]> wrote in message
news:[email protected]...
> 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
> >
> >
> >

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

"L. S. Martin" <[email protected]> wrote in message
news:[email protected]...
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
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> 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
>
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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'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
>
>
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>