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