find and return adjacent value (redux)!

[Guest]

I posted this earlier, but missed a key element in the example.

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 2 Tim 4
222 ABB Tony 6 Todd 5
333 BBB Tim 8 Sue 3 Jim 7
x=222, y=Tony, returned value = 6
x=111 y=Tim, returned value = 4
x=333 y=Jim, returned value = 7

Thanks in advance!

 

[Guest]

Assuming your table is in A1:H3 and A11=222 and B11=Tony, this returns the
results you're after:

=INDEX(INDEX($A$1:$H$3,MATCH(A11,$A$1:$A$3,0),0),MATCH(B11,INDEX($A$1:$H$3,MATCH(A11,$A$1:$A$3,0),0),0)+1)

There may be a shorter way, but it's past my bedtime.

 

[Guest]

This requires DGet function. It's a Database and list management function.
The syntax for DGet function is:
DGET(database,field,criteria)
To turn to your case:
1. You have to create a range to fill the criteria
1.1. Criteria is nothing but the conditions you apply to restrict the result.
Say, x=222, y=Tony etc.,
2. Database is the range where you search results for your criteria
2.2. Remember the both the Database and criteria column headers should be
unique
3. Field is the column value that you want to return (result)
For more help go to Help menu.

 

[RagDyer]

It would be better if you stayed with your original thread and added
additional information there.

The formula I suggested in that thread will do exactly as you're describing
here.
All you have to do is revise the ranges to meet the stipulations you posted
here.

From your original thread:
<<<"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
So, with your new datalist configuration,
Try this *array* formula,
where you enter the "x" value in J1,
and the "y" value in J2:

=INDEX(A1:H3,MATCH(J1,A1:A3,0),MAX((A1:H3=J2)*COLUMN(A1:H3))+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.