find certain value on a sheet and return value of cell 2 to the right

**jct77** · 04-22-2010, 05:34 PM

I am trying to find a way to search through an entire sheet (or selected array) and then return the value of the cell that is two to the right of the cell that is found. I have attached a spreadsheet as an example of what I want. Any help would be greatly appreciated!

**DonkeyOte** · 04-22-2010, 06:23 PM

A relatively expensive single cell formula approach I'm afraid:

Please Login or Register  to view this content.

confirmed with CTRL + SHIFT + ENTER

Given the above it generally makes sense to store the co-ordinates (eg ROW.COLUMN) in one cell and then retrieve values in another cell based on this value to avoid the repetitive calculation.

Alternatively use a User Defined Function (ie VBA).

**sailepaty** · 04-22-2010, 07:17 PM

Another kind of expensive formula.

=INDEX(A1:D4, INDEX(MAX((A1:D4="AL")*ROW(A1:D4)),0),2+MATCH("AL",INDEX(A1:D4,INDEX(MAX((A1:D4="AL")*ROW(A1:D4)),0),0),0))

Or following DonkeOte's suggestion. Set this in a helper cell, let's say D12.

=INDEX(MAX(($A$1:$D$4="AL")*ROW($A$1:$D$4)),0)

Then use this to return the value you are looking for.

=INDEX(A1:D4,D12,2+MATCH("AL",INDEX(A1:D4,D12,0),0))

Regards