I need help finding a formula for column G to help determine the price of the items, as the price depends on the quantity in column F.
thank you
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)
Accepted Solutions
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)