Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup, one row down
Hi,
I have a large database in an Excel file containing order information. I need to match delivery times from this database with some estimated production times per order number. In some cases there are two delivery times but the order number in the databsae is the same. Does anyone have an idea on how to also get the other delivery time, which always is found on the row under the first desired row. Kind of Vlookup(a1;Range;Row;0)+1 -- Tomas S |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup, one row down
=index(a1:a1000,match(a1,b1:b1000,0)+1)
"Tomas Stroem" wrote: Hi, I have a large database in an Excel file containing order information. I need to match delivery times from this database with some estimated production times per order number. In some cases there are two delivery times but the order number in the databsae is the same. Does anyone have an idea on how to also get the other delivery time, which always is found on the row under the first desired row. Kind of Vlookup(a1;Range;Row;0)+1 -- Tomas S |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup, one row down
What if there is just a single instance of the lookup value? If you always
look for the 2nd instance then in those cases when there is just a single instance the formula will return incorrect results. This will find the 2nd instance: =INDEX(range,MATCH(lookup_value,lookup_array,0)+1) -- Biff Microsoft Excel MVP "Tomas Stroem" wrote in message ... Hi, I have a large database in an Excel file containing order information. I need to match delivery times from this database with some estimated production times per order number. In some cases there are two delivery times but the order number in the databsae is the same. Does anyone have an idea on how to also get the other delivery time, which always is found on the row under the first desired row. Kind of Vlookup(a1;Range;Row;0)+1 -- Tomas S |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup, one row down
And if the data is not sorted/has more than 1 and you want the last selection from the array use this array function (after entering your formula press ctrl+shift+Enter instead of just Enter) =LOOKUP(2,1/(b1:b1000=a1),a1:a1000) "Tomas Stroem" wrote: Hi, I have a large database in an Excel file containing order information. I need to match delivery times from this database with some estimated production times per order number. In some cases there are two delivery times but the order number in the databsae is the same. Does anyone have an idea on how to also get the other delivery time, which always is found on the row under the first desired row. Kind of Vlookup(a1;Range;Row;0)+1 -- Tomas S |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup, one row down
Great help,
Thank you so much to the two of you!! -- Tomas S "N Harkawat" wrote: And if the data is not sorted/has more than 1 and you want the last selection from the array use this array function (after entering your formula press ctrl+shift+Enter instead of just Enter) =LOOKUP(2,1/(b1:b1000=a1),a1:a1000) "Tomas Stroem" wrote: Hi, I have a large database in an Excel file containing order information. I need to match delivery times from this database with some estimated production times per order number. In some cases there are two delivery times but the order number in the databsae is the same. Does anyone have an idea on how to also get the other delivery time, which always is found on the row under the first desired row. Kind of Vlookup(a1;Range;Row;0)+1 -- Tomas S |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |