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

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

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

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

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