How do I solve a vlookup when multiple records are available?
 

From an other sheet, I'm trying to look up a value in a list hoping to get a
value on the same record returned.
However, in the list where I'm looking, the basic identifier can occur
multiple times, related to a date. So, I wrote my lookup as follows:
=IF(VLOOKUP(DV14;[Functies.xls]Functionweight!$A$2:$E$225;1)=DV14;IF(VLOOKUP(DV14 ;[Functies.xls]Functionweight!$A$2:$E$225;4)=$B$5;VLOOKUP(DV14;[Functies.xls]Functionweight!$A$2:$E$225;5);"FALSE"))
In this case, DV14 contains my identifier in my "receiving" sheet. In the
"functionweight" sheet, the identifier can occur multiple times (in row 1),
so the second "IF" looks for the corresponding cell (column 4), which has to
be identical to B5 in my receiving sheet (which contains the dummy date). If
both conditions are true, I want to receive the value of column 5 back.
What happens is that when e.g. 2 records are available (one with a date in
the past, and one with the dummy date in column 4), I get an error back in
stead of the value in column 5 of the row containing the dummy date .... Does
anyone have a bright idea on how this can be solved ?

Thanks in advance,
Chris

How do I solve a vlookup when multiple records are available?
 

Hi,

Try something like

=SUMPRODUCT(--([Functies.xls]Functionweight!$A$2:$A$225=DV14),--(DateColumn=DU14),[Functies.xls]Functionweight!$E$2:$E$225)

Where DateColumn is the date column in the lookup sheet and DU14 contains
the date you want.

If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Chris" wrote:

From an other sheet, I'm trying to look up a value in a list hoping to get a
value on the same record returned.
However, in the list where I'm looking, the basic identifier can occur
multiple times, related to a date. So, I wrote my lookup as follows:
=IF(VLOOKUP(DV14;[Functies.xls]Functionweight!$A$2:$E$225;1)=DV14;IF(VLOOKUP(DV14 ;[Functies.xls]Functionweight!$A$2:$E$225;4)=$B$5;VLOOKUP(DV14;[Functies.xls]Functionweight!$A$2:$E$225;5);"FALSE"))
In this case, DV14 contains my identifier in my "receiving" sheet. In the
"functionweight" sheet, the identifier can occur multiple times (in row 1),
so the second "IF" looks for the corresponding cell (column 4), which has to
be identical to B5 in my receiving sheet (which contains the dummy date). If
both conditions are true, I want to receive the value of column 5 back.
What happens is that when e.g. 2 records are available (one with a date in
the past, and one with the dummy date in column 4), I get an error back in
stead of the value in column 5 of the row containing the dummy date .... Does
anyone have a bright idea on how this can be solved ?

Thanks in advance,
Chris

How do I solve a vlookup when multiple records are available?
 

Hi,

Please check this link.

http://office.microsoft.com/en-us/ex...260381033.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Chris" wrote in message
...
From an other sheet, I'm trying to look up a value in a list hoping to get
a
value on the same record returned.
However, in the list where I'm looking, the basic identifier can occur
multiple times, related to a date. So, I wrote my lookup as follows:
=IF(VLOOKUP(DV14;[Functies.xls]Functionweight!$A$2:$E$225;1)=DV14;IF(VLOOKUP(DV14 ;[Functies.xls]Functionweight!$A$2:$E$225;4)=$B$5;VLOOKUP(DV14;[Functies.xls]Functionweight!$A$2:$E$225;5);"FALSE"))
In this case, DV14 contains my identifier in my "receiving" sheet. In the
"functionweight" sheet, the identifier can occur multiple times (in row
1),
so the second "IF" looks for the corresponding cell (column 4), which has
to
be identical to B5 in my receiving sheet (which contains the dummy date).
If
both conditions are true, I want to receive the value of column 5 back.
What happens is that when e.g. 2 records are available (one with a date in
the past, and one with the dummy date in column 4), I get an error back in
stead of the value in column 5 of the row containing the dummy date ....
Does
anyone have a bright idea on how this can be solved ?

Thanks in advance,
Chris