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?