Lookup Problem

[Adam L]

I have an array of data. I want to use a lookup function to find a
particular value, then produce the value directly below this reference. For
example if I'm looking for the value in A4 I want A5 returned. Since the
data is irregular HLOOKUP will not work because the value I'm searching for
is not in the top row. Vlookup returns the cell to the right of my value,
but not below. I've tried the offest function but can't seem to make it
happen. Any ideas?

 

[Chip Pearson]

You can use OFFSET for this. E.g.,

=OFFSET($A$1,MATCH("e",A1:A10,0),0)

The MATCH function uses 1-based offsets while OFFSET uses 0-based
offsets, so the value returned by MATCH to OFFSET will be one cell
below where the value is found.

If you want to avoid the #N/A error if the value isn't found, use

=IF(COUNTIF(A1:A10,"e"),OFFSET($A$1,MATCH("e",A1:A10,0),0),"Not
Found")

Replace the text "Not Found" with whatever you want to display if no
match is found.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Wed, 18 Feb 2009 13:36:02 -0800, Adam L <Adam

 

[~L]

=INDEX(Results Row,MATCH(Search Value, Search Row, 0))

 

[Lars-Åke Aspelin]

On Wed, 18 Feb 2009 13:36:02 -0800, Adam L <Adam

I have an array of data. I want to use a lookup function to find a
particular value, then produce the value directly below this reference. For
example if I'm looking for the value in A4 I want A5 returned. Since the
data is irregular HLOOKUP will not work because the value I'm searching for
is not in the top row. Vlookup returns the cell to the right of my value,
but not below. I've tried the offest function but can't seem to make it
happen. Any ideas?

Try a combination of INDEX() and MATCH() functions.

If the value you are looking for in the range A1:A100 is in cell E5

=INDEX(A1:A100,MATCH(E5,A1:A100,0)+1)

will return the value in the cell directly under the cell where you
get a match.

Hope this helps / Lars-Åke