Choose Top 2 Supplier out of 5 Supplier list

[arnabbhuyan]

Hi Team,
I am looking for a solution which helps me to find out Top 2 Supplier out of 5 Supplier list provided by user with some criteria for selection.

Logic is as follows:

if in Q15 Metro is mentioned as #1 supplier, set Metro #1 and ask for supplier #2.
If Metro is not mentioned as #1, please set #1 Supplier from Q15 as #1 and set Metro #2. If a supplier is indicated as supplying only beverages in Q16, please take the next best supplier that is not supplying beverages only.
if nothing is mentioned in Supplier column(B) then by default set Supplier #2 = Metro

I have used formula to calculate but the formula for Supplier #2 always returns Metro(column num B13)

Logic written for B12(Supplier #1) --> =IF(C3<"x",B3,IF(C4<"x",B4,IF(C5<"x",B5,IF(C6<"x",B6,IF(C7<"x",B7)))))
Logic for B13(Supplier #2) --> =IF(OR(B12<>"metro"),B3,IF(OR(C4<"x",B4="metro"),B5,IF(OR(C5<"x",B5="metro"),B6,IF(OR(C6<"x",B6="metro"),B7,IF(OR(C7<"x",B7="metro"),B4)))))



Thanks,
Arnab