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

Dan5977

Board Regular
Joined
Aug 7, 2008
Messages
106
Office Version
  1. 2010
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
Joined
Jun 12, 2014
Messages
60,134
Office Version
  1. 365
Platform
  1. Windows
You could use the Filter function, but it won't work in merged cells.
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,134
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

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