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.
What is the simplest way of doing this please?
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 | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
9 | Solution A | No | ||||
10 | Solution B | No | ||||
11 | Solution C | No | ||||
12 | Solution D | No | ||||
13 | Solution E | No | ||||
14 | Solution F | No | ||||
15 | Solution G | No | ||||
16 | Solution H | Yes | ||||
17 | Solution I | Yes | ||||
18 | Solution J | No | ||||
19 | Solution K | No | ||||
20 | Solution L | No | ||||
21 | Solution M | No | ||||
22 | Solution N | No | ||||
23 | Solution O | No | ||||
24 | Solution P | No | ||||
25 | Solution Q | No | ||||
26 | Solution R | No | ||||
27 | Solution S | No | ||||
28 | Solution T | No | ||||
29 | Solution U | No | ||||
30 | Solution V | No | ||||
31 | Solution W | No | ||||
32 | Solution X | No | ||||
33 | Solution Y | No | ||||
34 | Solution Z | No | ||||
35 | Solution AA | No | ||||
36 | Solution AB | No | ||||
37 | ||||||
38 | Prefered Solution | Solution H | ||||
39 | Alternative Solution 1 | |||||
40 | Alternative Solution 2 | |||||
Bolted Base |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C9 | C9 | =IF(C$2="127 x 76 x 13 UB",IF(C$6 = "Pass",IF(C$7<W9,"Yes","No"),"No"),"No") |
C10 | C10 | =IF(C$2="152 x 89 x 16 UB",IF(C$6 = "Pass",IF(C$7<W10,"Yes","No"),"No"),"No") |
C11:C12 | C11 | =IF(C$2="178 x 102 x 19 UB",IF(C$6 = "Pass",IF(C$7<W11,"Yes","No"),"No"),"No") |
C13 | C13 | =IF(C$2="203 x 102 x 23 UB",IF(C$6 = "Pass",IF(C$7<W13,"Yes","No"),"No"),"No") |
C14:C15 | C14 | =IF(C$2="254 x 102 x 22 UB",IF(C$6 = "Pass",IF(C$7<W14,"Yes","No"),"No"),"No") |
C16:C17 | C16 | =IF(C$2="254 x 102 x 25 UB",IF(C$6 = "Pass",IF(C$7<W16,"Yes","No"),"No"),"No") |
C18 | C18 | =IF(C$2="305 x 102 x 25 UB",IF(C$6 = "Pass",IF(C$7<W18,"Yes","No"),"No"),"No") |
C19 | C19 | =IF(C$2="254 x 102 x 28 UB",IF(C$6 = "Pass",IF(C$7<W19,"Yes","No"),"No"),"No") |
C20 | C20 | =IF(C$2="305 x 102 x 28 UB",IF(C$6 = "Pass",IF(C$7<W20,"Yes","No"),"No"),"No") |
C21 | C21 | =IF(C$2="305 x 102 x 33 UB",IF(C$6 = "Pass",IF(C$7<W21,"Yes","No"),"No"),"No") |
C22 | C22 | =IF(C$2="356 x 127 x 33 UB",IF(C$6 = "Pass",IF(C$7<W22,"Yes","No"),"No"),"No") |
C23 | C23 | =IF(C$2="305 x 127 x 37 UB",IF(C$6 = "Pass",IF(C$7<W23,"Yes","No"),"No"),"No") |
C24 | C24 | =IF(C$2="356 x 127 x 39 UB",IF(C$6 = "Pass",IF(C$7<W24,"Yes","No"),"No"),"No") |
C25 | C25 | =IF(C$2="406 x 140 x 39 UB",IF(C$6 = "Pass",IF(C$7<W25,"Yes","No"),"No"),"No") |
C26 | C26 | =IF(C$2="406 x 140 x 46 UB",IF(C$6 = "Pass",IF(C$7<W26,"Yes","No"),"No"),"No") |
C27:C28 | C27 | =IF(C$2="457 x 152 x 52 UB",IF(C$6 = "Pass",IF(C$7<W27,"Yes","No"),"No"),"No") |
C29 | C29 | =IF(C$2="457 x 152 x 60 UB",IF(C$6 = "Pass",IF(C$7<W29,"Yes","No"),"No"),"No") |
C30:C31 | C30 | =IF(C$2="457 x 152 x 67 UB",IF(C$6 = "Pass",IF(C$7<W30,"Yes","No"),"No"),"No") |
C32:C34 | C32 | =IF(C$2="457 x 152 x 74 UB",IF(C$6 = "Pass",IF(C$7<W32,"Yes","No"),"No"),"No") |
C35:C36 | C35 | =IF(C$2="457 x 152 x 82 UB",IF(C$6 = "Pass",IF(C$7<W35,"Yes","No"),"No"),"No") |
C38 | C38 | =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?