# Lookup on Each Duplicate Value in Excel

#### dickpierce

##### Board Regular
Hello!

If I use the formula in H2 and bring it down to H7, I get the desired results, unfortunately when it reaches H8 I get the #NUM result. I know that if I change the ROWS(F\$2:F8) to ROWS(F\$8:F8) I get the required result, but I'm just wondering if there's a way that would do it automatically. Thanks in advance for your help.

F G H

1 Alabama 6 ={INDEX(\$C\$2:\$C\$1800,SMALL(IF(\$A\$2:\$A\$1800=F2,ROW(A\$2:\$A\$1800)-1),ROWS(F\$2:F2)))}
1 Alabama 2
1 Alabama 15
1 Alabama 8
1 Alabama 13
1 Alabama 3
2 Ohio St #NUM !
2 Ohio St #NUM !
2 Ohio St #NUM !
2 Ohio St #NUM !
2 Ohio St #NUM !
2 Ohio St #NUM !
3 Georgia #NUM !
3 Georgia #NUM !
3 Georgia #NUM !
3 Georgia #NUM !
3 Georgia #NUM !
3 Georgia #NUM !

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Maybe this array formula:

Code:
``````[TABLE="width: 762"]
<colgroup><col width="762"></colgroup><tbody>[TR]
[TD="width: 762"]=INDEX(\$C\$2:\$C\$1800,SMALL(IF(\$A\$2:\$A\$1800=F2,ROW(A\$2:\$A\$1800)-1),COUNTIF(\$F\$2:\$F2,F2)))[/TD]
[/TR]
</tbody>[/TABLE]``````

Excellent!!! Worked like a charm! Thanks so much!

You're welcome. Thanks for the feedback.

Replies
7
Views
166
Replies
2
Views
176
Replies
4
Views
200
Replies
5
Views
343
Replies
0
Views
449

1,196,287
Messages
6,014,486
Members
441,822
Latest member
slozcannon

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