2 way Lookup array formula with Duplicates

Kankuroo

New Member
Joined
May 2, 2014
Messages
5
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 " - ")


ABCDE
1JanFebMarApr
2Jane150150 - -
3Bob150150150150
4Kim
-200200200
5Tom -120120120
6Sam -300300300
7Jane - -150150

<tbody>
</tbody>

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

AB
9Mar
10Jane -
11Sam300

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

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

johnmpl

Board Regular
Joined
Jun 14, 2013
Messages
235
Hi!

This is the Entry Data:
ABCDE
1JanFebMarApr
2Jane150150--
3Bob150150150150
4Kim-200200200
5Tom-120120120
6Sam-300300300
7Jane--150150

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

And the desire results:

AB
9Mar
10Jane150
11Sam300

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

Worksheet Formulas
CellFormula
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!
 

Forum statistics

Threads
1,143,658
Messages
5,720,128
Members
422,267
Latest member
olund

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top