Excel finds last match from two tables

Question

I am looking for a way to find the last instance to write in columns A, B and get the corresponding values ​​from columns C, D

In the example below, the value for Henry is 1374 and the value for Amy is 1124

Name1 corresponds to Value1, and Name2 corresponds to Value2. Is there a formula to find the last record from both columns Name1 and Name2 and return the corresponding values ​​Value1 or Value2

Raw data inserted below:

Name1   Name2   Value1  Value2
Sara    Amy     1265    1241
John    Sara    1142    1214
Amy     Henry   1295    1121
Amy     John    1175    1323
Sara    John    1085    1251
Sara    Henry   1242    1374
Amy     Sara    1124    1055

Answer 1

Assumptions:

The data grid is in cells A1:D8. The values ​​"Henry" and "Amy" are in the cell A10and, A11respectively.

The implementation of the formula:

B10 .

1:

=INDEX($C$2:$D$8,MAX(IFERROR(LOOKUP(2,1/($A$2:$A$8=A10),ROW($A$2:$A$8)),-1),IFERROR(LOOKUP(2,1/($B$2:$B$8=A10),ROW($B$2:$B$8)),-1))-1,IF(IFERROR(LOOKUP(2,1/($A$2:$A$8=A10),ROW($A$2:$A$8)),-1)>IFERROR(LOOKUP(2,1/($B$2:$B$8=A10),ROW($B$2:$B$8)),-1),1,2))

2 ( 1):

=INDEX($C$2:$D$8,LOOKUP(2,1/SEARCH(A10&",",$A$2:$A$8&","&$B$2:$B$8&",",1),ROW($A$2:$A$8))-1,IF(IFERROR(LOOKUP(2,1/($A$2:$A$8=A10),ROW($A$2:$A$8)),-1)>IFERROR(LOOKUP(2,1/($B$2:$B$8=A10),ROW($B$2:$B$8)),-1),1,2)).

.

-1 MAX(), . n-1, , n- .

Answer 2

:

= INDEX($C$1:$D$8,MAX(IF($A$1:$B$8=A10,ROW($A$1:$B$8))),MATCH(A10,
  INDEX($A$1:$B$8,MAX(IF($A$1:$B$8=A10,ROW($A$1:$B$8))),0),0))

, , Ctrl + Shift + Enter, Enter .

. .

Answer 3

, 2d ( ).

=INDIRECT(TEXT(MAX((ROW($A$2:$B$8)*100+COLUMN($A$2:$B$8))*($A$2:$B$8=A10))+2,"R0C00"),FALSE)

Ctrl Shift Enter.

, , , ( 702).

You format it to give R7C02 and pass it indirectly to give a cell reference in RC notation. Column plus 2 gives the cell you want.

You may notice that this happens if the column is> 99, but you can make the factor as large as possible.