HLookup but searching for the nearest number, not necessarily the exact match

[Neilads]

Hello,

I am in need of a clever formula that can help me cost the the number of A3 sheets, when we are printing A4s on them (2up). My current formula only calculates for sheets when they are A3 size...

I am looking for a way to find the cost of the paper sheet from the C column for the A4 Sizes (to be displaed in column H). The issue is that we can get 2 x A4 out of an A3, and when we print 500 A4s, we only use 250 A3s, therefore the calculation needs to say the sheet cost when using 250 sheets, not 500. The column that needs to have the formula in is Column H. I tried HLookup, but it only returned exact matches, where it could drop down to the nearest value below when it doesn't find an exact match. For example, for 350 A4s, 177 sheets will be used. There isn't a value in the B column that shows 177, but the nearest below it is 172, which would be fine to use.

Can you help?

 

[Fluff]

To get the next lower value, try

=XLOOKUP(G3,$B$3:$B$1000,$C$3:$C$1000,"",-1)

 

[Neilads]

Amazing! That's worked a treat. Thank you so much.
It's hard enough knowing how to ask the question, but you've solved the riddle for me and that really helps, so thank you.

 

[Fluff]

You're welcome & thanks for the feedback.