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 " - ")


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jane[/TD]
[TD]150[/TD]
[TD]150[/TD]
[TD] -[/TD]
[TD] -[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Bob[/TD]
[TD]150[/TD]
[TD]150[/TD]
[TD]150[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Kim
[/TD]
[TD] -[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Tom[/TD]
[TD] -[/TD]
[TD]120[/TD]
[TD]120[/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Sam[/TD]
[TD] -[/TD]
[TD]300[/TD]
[TD]300[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Jane[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD]150[/TD]
[TD]150[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]Mar[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Jane[/TD]
[TD] -[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Sam[/TD]
[TD]300[/TD]
[/TR]
</tbody>[/TABLE]


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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Jan[/TD]
[TD="align: center"]Feb[/TD]
[TD="align: center"]Mar[/TD]
[TD="align: center"]Apr[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Jane[/TD]
[TD="align: center"]150[/TD]
[TD="align: center"]150[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]Bob[/TD]
[TD="align: center"]150[/TD]
[TD="align: center"]150[/TD]
[TD="align: center"]150[/TD]
[TD="align: center"]150[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]Kim[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]200[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]Tom[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]120[/TD]
[TD="align: center"]120[/TD]
[TD="align: center"]120[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]Sam[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]300[/TD]
[TD="align: center"]300[/TD]
[TD="align: center"]300[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]Jane[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]150[/TD]
[TD="align: center"]150[/TD]

</tbody>

Hoja1​

And the desire results:

	A	B

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Mar[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]Jane[/TD]
[TD="align: center"]150[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]Sam[/TD]
[TD="align: center"]300[/TD]

</tbody>

Hoja1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B10[/TH]
[TD="align: left"]=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,))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B11[/TH]
[TD="align: left"]=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,))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[Kankuroo]

Hi John,

Thank you very much! This worked like a charme!