find and return adjacent value
 

How would I do the following using a formula:

Find the row that contains value X, then in that row find the cell that
contains value Y, then return the value to the immediate right?

Example:
111 AAA Todd Tim
222 ABB Tony Tom
333 BBB Sue Shelly
x=222, y=Tony, returned value = Tom

Thanks in advance!

find and return adjacent value
 

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=INDEX(D1:D100,MATCH(x&y,A1:A100&C1:C100,FALSE))

In article ,
Todd Lietha wrote:

How would I do the following using a formula:

Find the row that contains value X, then in that row find the cell that
contains value Y, then return the value to the immediate right?

Example:
111 AAA Todd Tim
222 ABB Tony Tom
333 BBB Sue Shelly
x=222, y=Tony, returned value = Tom

Thanks in advance!

find and return adjacent value
 

If you know that the "x" is in Column A, but you *don't* know what column
contains the "y",
Try this *array* formula,
where you enter the "x" value in E1,
and the "y" value in E2:

=INDEX(A1:D3,MATCH(E1,A1:A3,0),MAX((A1:D3=E2)*COLU MN(A1:D3))+1)

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Todd Lietha" wrote in message
...
How would I do the following using a formula:

Find the row that contains value X, then in that row find the cell that
contains value Y, then return the value to the immediate right?

Example:
111 AAA Todd Tim
222 ABB Tony Tom
333 BBB Sue Shelly
x=222, y=Tony, returned value = Tom

Thanks in advance!