Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I consolidate multiple records into one? | Excel Discussion (Misc queries) | |||
Multiple records per row | Excel Discussion (Misc queries) | |||
return multiple records matching multiple criteria | Excel Worksheet Functions | |||
Can VLookup function find and list multiple records? | Excel Worksheet Functions | |||
How can I solve for multiple unknown variables in Excel? | Excel Discussion (Misc queries) |