# 2 way Lookup array formula with Duplicates

#### Kankuroo

##### New Member
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!

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Hi!

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

</tbody>
Hoja1

And the desire results:

AB
9Mar
10Jane150
11Sam300

</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,))

</tbody>

<tbody>
</tbody>

I hope it helps! God Bless You!

Hi John,

Thank you very much! This worked like a charme!

Replies
3
Views
194
Replies
16
Views
314
Replies
1
Views
96
Replies
9
Views
381
Replies
4
Views
88

1,219,819
Messages
6,150,405
Members
450,960
Latest member
GB2

### 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.

### Which adblocker are you using?

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

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