# 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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### johnmpl

##### Board Regular
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!

#### Kankuroo

##### New Member
Hi John,

Thank you very much! This worked like a charme!

Replies
1
Views
255
Replies
1
Views
88
Replies
6
Views
168
Replies
1
Views
76
Replies
7
Views
165

1,148,337
Messages
5,746,170
Members
423,998
Latest member
eakenila

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