look up functions

[JM]

I am trying to set up a formula to look for a word such as "sector" in column
A and then give me the value of the data that is one row below the word and
one column over. For example, the word "sector" will be in a dynamic range
in column A. If it is in cell A250, I would like to get the data in cell
B251. How can I do this?

 

[T. Valko]

One way:

D1 = sector

=INDEX(B1:B100,MATCH(D1,A1:A100,0)+1)

 

[JM]

This formula gave me the value in column B, but it was the value on the same
row, not one row below.

 

[T. Valko]

...........A..........B
1........X.........22
2....................50
3........Y.........10
4....................20

Lookup Y, return the value 1 column to the right and 1 row down:

=INDEX(B1:B4,MATCH("y",A1:A4,0)+1)

Result = 20

Or, this formula (but the formula above is better!):

=OFFSET(B1,MATCH("y",A1:A4,0),)

Result = 20

 

[JM]

Thanks!
--
JM

...........A..........B
1........X.........22
2....................50
3........Y.........10
4....................20

Lookup Y, return the value 1 column to the right and 1 row down:

=INDEX(B1:B4,MATCH("y",A1:A4,0)+1)

Result = 20

Or, this formula (but the formula above is better!):

=OFFSET(B1,MATCH("y",A1:A4,0),)

Result = 20

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thanks!