Vlookup and retrieve values 1 row below the normally retrieved val
 

I'm trying to figure out how to retrieve a value 1 row below the value that
would normally be retrieved with a regular vlookup. As an example below,
using 1234 as my lookup value, column A-B as the table array, column index
=2, and range lookup ="false" I'd like to return the value "5" instead of
"2". Can this be done somehow through an formula?

COL A COL B
Item # Sales
1234 2
5
3
4
2345 6
7
9
1

Vlookup and retrieve values 1 row below the normally retrieved val
 

Try this...

D2 = lookup value = 1234

=INDEX(B2:B9,MATCH(D2,A2:A9,0)+1)

--
Biff
Microsoft Excel MVP


"Jon Ratzel" wrote in message
...
I'm trying to figure out how to retrieve a value 1 row below the value
that
would normally be retrieved with a regular vlookup. As an example below,
using 1234 as my lookup value, column A-B as the table array, column index
=2, and range lookup ="false" I'd like to return the value "5" instead of
"2". Can this be done somehow through an formula?

COL A COL B
Item # Sales
1234 2
5
3
4
2345 6
7
9
1

Vlookup and retrieve values 1 row below the normally retrieved val
 

Try a formula like the following:

=OFFSET(A2,MATCH(234,A2:A100,0),1,1,1)

where 234 is the value to look up in cells A2:A100. Change as needed.
it will return #N/A if 234 is not found in A2;A100.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]





On Wed, 20 Jan 2010 11:40:01 -0800, Jon Ratzel
wrote:

I'm trying to figure out how to retrieve a value 1 row below the value that
would normally be retrieved with a regular vlookup. As an example below,
using 1234 as my lookup value, column A-B as the table array, column index
=2, and range lookup ="false" I'd like to return the value "5" instead of
"2". Can this be done somehow through an formula?

COL A COL B
Item # Sales
1234 2
5
3
4
2345 6
7
9
1