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

Dan5977

Board Regular
Joined
Aug 7, 2008
Messages
108
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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
You could use the Filter function, but it won't work in merged cells.
 
Upvote 0
Solution
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

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
Back
Top