Forum Discussion
help finding a formula
It would better if you add a sample sheet so that we can work on that. Also showing desired output helps visitor to understand expectation clearly. However, it seems you are looking to find the unit price as per quantity volume. So, it order quantity is exact as bundle then it will show price from that bundle or it will show price from smaller bundle. If so, then use below XLOOKUP() formula.
=XLOOKUP(F6,$B$5:$E$5,B6:E6,"",-1)If you need to show price from next larger bundle then use match_mode "Next_Larger_Item" in xlookup parameter with 1. Then use-
=XLOOKUP(F6,$B$5:$E$5,B6:E6,"",1)
It would better if you add a sample sheet so that we can work on that. Also showing desired output helps visitor to understand expectation clearly. However, it seems you are looking to find the unit price as per quantity volume. So, it order quantity is exact as bundle then it will show price from that bundle or it will show price from smaller bundle. If so, then use below XLOOKUP() formula.
=XLOOKUP(F6,$B$5:$E$5,B6:E6,"",-1)
If you need to show price from next larger bundle then use match_mode "Next_Larger_Item" in xlookup parameter with 1. Then use-
=XLOOKUP(F6,$B$5:$E$5,B6:E6,"",1)