SOLVED

help finding a formula

Copper Contributor

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

Screenshot excel.jpg

1 Reply
best response confirmed by akiva1 (Copper Contributor)
Solution

@akiva1 

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)

 

Harun24HR_0-1659060952976.png

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)

 

1 best response

Accepted Solutions
best response confirmed by akiva1 (Copper Contributor)
Solution

@akiva1 

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)

 

Harun24HR_0-1659060952976.png

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)

 

View solution in original post