find and return adjacent value

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=INDEX(D1:D100,MATCH(x&y,A1:A100&C1:C100,FALSE))
 
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)*COLUMN(A1:D3))+1)
 
Back
Top