2 way Lookup array formula with Duplicates

[Kankuroo]

Hi All,

I am am performing a 2 way lookup within the array below, based on "Month" and "Name", to return the intersecting value/blank.
This works fine, however I need it to return the last value if the "name" Column has a duplicate. (For March I want Jane to display 150, instead of " - ")

	A	B	C	D	E
1		Jan	Feb	Mar	Apr
2	Jane	150	150	-	-
3	Bob	150	150	150	150
4	Kim	-	200	200	200
5	Tom	-	120	120	120
6	Sam	-	300	300	300
7	Jane	-	-	150	150

<tbody>
</tbody>

The formula I have been using is =VLOOKUP(A10,A2:E7,MATCH($B$9,$A$1:$E$1,0),0)

	A	B
9		Mar
10	Jane	-
11	Sam	300

<tbody>
</tbody>

My problem is when there is a Duplicate in the list of Names, The first value in the array is the result.

Do anyone know a way around this, and returning the value of the last Name?

Any input is much appreciated!

 

[johnmpl]

Hi!

This is the Entry Data:
​

	A	B	C	D	E
1		Jan	Feb	Mar	Apr
2	Jane	150	150	-	-
3	Bob	150	150	150	150
4	Kim	-	200	200	200
5	Tom	-	120	120	120
6	Sam	-	300	300	300
7	Jane	-	-	150	150

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Hoja1​

And the desire results:

	A	B
9		Mar
10	Jane	150
11	Sam	300

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Hoja1​

Worksheet Formulas Cell Formula B10 =INDEX($B$2:$E$7,MAX(INDEX(($A$2:$A$7=$A10)*(ROW($A$2:$A$7)-ROW($A$2)+1),)),MATCH(B$9,$B$1:$E$1,)) B11 =INDEX($B$2:$E$7,MAX(INDEX(($A$2:$A$7=$A11)*(ROW($A$2:$A$7)-ROW($A$2)+1),)),MATCH(B$9,$B$1:$E$1,)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Please Comment!
I hope it helps! God Bless You!

 

[Kankuroo]

Hi John,

Thank you very much! This worked like a charme!