Lookup vertically

[hugh]

In cell C25 I would like to place a formula that finds the closest cell
UP in Column A that contains the word "Arizona", and then returns the
value in column B for that row. For example: if A19 contains "Arizona"
and B19 contains 34.... and A14 contains "Arizona" and B14 contains 38,
I would like the formula in cell C25 to return 34--because row 19 is
closer to row 25 than row 14 is to row 25. (assume the other rows in
column A contain names of different states). Thanks very much to whoever
can help!!!

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **

 

[Jerry W. Lewis]

=INDIRECT("B"&MAX(IF(A$1:A24="Arizona",ROW(A$1:A24))))

array entered (Ctrl-Shift-Enter)

Jerry

 

[hmillen]

Jerry,
Thanks very much for the help. I did, however, receive a message that
said, "array formulas are not valid in merged cells."
I had copied your formula into cell C25 and pressed ctrl+shift+enter.
Did I make some mistake? Thanks again.
Hugh

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **

 

[Dave Peterson]

Either unmerge C25 (format|cells|alignment tab)
or put the formula in a different cell.

If you must have C25 merged, you can use a different (out of the way cell) to
determine the value (z99?), then use the formula =z99 in cell c25.

 

[hmillen]

Dave:
Thank you for your help. In the alignment tab, the cells are not merged.
I have moved the formula to a more remote cell. I still am getting the
same error message. Am I still doing something wrong? Thank you very
much.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **

 

[hmillen]

Dave,
Never mind!!
It works!!
Thank you both!!

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **