# Find 2nd, 3rd, nth match in a lookup

#### Dan5977

##### Board Regular
Hi all,

I'm fairly new to Office365 and the additional Excel functions that provides us with.

I'm hoping someone can tell me there is now a simpler way of looking up multiple occurences.

See the example below where I want to return each valid option (shown as "Yes" in column 2). In this case, alternative solution 1 would be the 2nd match and so would say "Solution I". There would be no alternative solution 2 in this case but will be in others.

0321-061a.xlsm
ABCD
9Solution ANo
10Solution BNo
11Solution CNo
12Solution DNo
13Solution ENo
14Solution FNo
15Solution GNo
16Solution HYes
17Solution IYes
18Solution JNo
19Solution KNo
20Solution LNo
21Solution MNo
22Solution NNo
23Solution ONo
24Solution PNo
25Solution QNo
26Solution RNo
27Solution SNo
28Solution TNo
29Solution UNo
30Solution VNo
31Solution WNo
32Solution XNo
33Solution YNo
34Solution ZNo
35Solution AANo
36Solution ABNo
37
38Prefered SolutionSolution H
39Alternative Solution 1
40Alternative Solution 2
Bolted Base
Cell Formulas
RangeFormula
C9C9=IF(C\$2="127 x 76 x 13 UB",IF(C\$6 = "Pass",IF(C\$7<W9,"Yes","No"),"No"),"No")
C10C10=IF(C\$2="152 x 89 x 16 UB",IF(C\$6 = "Pass",IF(C\$7<W10,"Yes","No"),"No"),"No")
C11:C12C11=IF(C\$2="178 x 102 x 19 UB",IF(C\$6 = "Pass",IF(C\$7<W11,"Yes","No"),"No"),"No")
C13C13=IF(C\$2="203 x 102 x 23 UB",IF(C\$6 = "Pass",IF(C\$7<W13,"Yes","No"),"No"),"No")
C14:C15C14=IF(C\$2="254 x 102 x 22 UB",IF(C\$6 = "Pass",IF(C\$7<W14,"Yes","No"),"No"),"No")
C16:C17C16=IF(C\$2="254 x 102 x 25 UB",IF(C\$6 = "Pass",IF(C\$7<W16,"Yes","No"),"No"),"No")
C18C18=IF(C\$2="305 x 102 x 25 UB",IF(C\$6 = "Pass",IF(C\$7<W18,"Yes","No"),"No"),"No")
C19C19=IF(C\$2="254 x 102 x 28 UB",IF(C\$6 = "Pass",IF(C\$7<W19,"Yes","No"),"No"),"No")
C20C20=IF(C\$2="305 x 102 x 28 UB",IF(C\$6 = "Pass",IF(C\$7<W20,"Yes","No"),"No"),"No")
C21C21=IF(C\$2="305 x 102 x 33 UB",IF(C\$6 = "Pass",IF(C\$7<W21,"Yes","No"),"No"),"No")
C22C22=IF(C\$2="356 x 127 x 33 UB",IF(C\$6 = "Pass",IF(C\$7<W22,"Yes","No"),"No"),"No")
C23C23=IF(C\$2="305 x 127 x 37 UB",IF(C\$6 = "Pass",IF(C\$7<W23,"Yes","No"),"No"),"No")
C24C24=IF(C\$2="356 x 127 x 39 UB",IF(C\$6 = "Pass",IF(C\$7<W24,"Yes","No"),"No"),"No")
C25C25=IF(C\$2="406 x 140 x 39 UB",IF(C\$6 = "Pass",IF(C\$7<W25,"Yes","No"),"No"),"No")
C26C26=IF(C\$2="406 x 140 x 46 UB",IF(C\$6 = "Pass",IF(C\$7<W26,"Yes","No"),"No"),"No")
C27:C28C27=IF(C\$2="457 x 152 x 52 UB",IF(C\$6 = "Pass",IF(C\$7<W27,"Yes","No"),"No"),"No")
C29C29=IF(C\$2="457 x 152 x 60 UB",IF(C\$6 = "Pass",IF(C\$7<W29,"Yes","No"),"No"),"No")
C30:C31C30=IF(C\$2="457 x 152 x 67 UB",IF(C\$6 = "Pass",IF(C\$7<W30,"Yes","No"),"No"),"No")
C32:C34C32=IF(C\$2="457 x 152 x 74 UB",IF(C\$6 = "Pass",IF(C\$7<W32,"Yes","No"),"No"),"No")
C35:C36C35=IF(C\$2="457 x 152 x 82 UB",IF(C\$6 = "Pass",IF(C\$7<W35,"Yes","No"),"No"),"No")
C38C38=IF(ISNA(VLOOKUP("Yes",C9:\$V\$36,C8,FALSE))=TRUE,"None",VLOOKUP("Yes",C9:\$V\$36,C8,FALSE))

What is the simplest way of doing this please?

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### Fluff

##### MrExcel MVP, Moderator
You could use the Filter function, but it won't work in merged cells.

#### Dan5977

##### Board Regular
You could use the Filter function, but it won't work in merged cells.
Thank you. Don't know why I didn't think of that!

I can make that work.

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
7
Views
162
Replies
3
Views
67
Replies
1
Views
65
Replies
7
Views
197
Replies
5
Views
71

1,136,799
Messages
5,677,807
Members
419,721
Latest member
StuckInWork

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