find and return adjacent value

[Guest]

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!

 

[JE McGimpsey]

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

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

 

[RagDyer]

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(A13,MATCH(E1,A1:A3,0),MAX((A13=E2)*COLUMN(A13))+1)