find and return adjacent value


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!
 
Ad

Advertisements

J

JE McGimpsey

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

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

Advertisements

R

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top