Finding the Nearest Value in a What-If Scenario

[tomtomtom]

Hi
I have a system on Excel, where the user will enter a Target Profit for a hotels sales over a fortnight.
I have created a what-if scenario with various different profit values using different percentages of discount for room rates etc.
On one page, where the Target Profit is inputted, I want to use a function on Excel to find the nearest value to the target profit from the what-if scenario results.

For example:
The user enters the target profit £13,000.
The formula will then check the target profit against the what-if scenario results and find the nearest value eg. £13,003


At the moment I am using HLOOKUP to find the nearest profit, which works to an extent but doesn't find the actual nearest value, only the nearest which isn't higher than the number inputted (target profit)

For example:
The user inputs £13,000 as the target profit.
The nearest value on the what-if scenario is £13,050, however, because it is higher than £13,000, the result of the HLOOKUP is £12,100.

If anyone could help I would be very grateful.

Cheers

[ChemistB]

There might be (probably is) an easier way but here's what I would do.
1. Switch from HLOOKUP to INDEX so your original formula (assuming value in A1 and range in A3:M3) would be

Please Login or Register  to view this content.

2. We need to compare the difference between the 1 above and the one below that number. The value above can be found by

Please Login or Register  to view this content.

Sooooooo

Please Login or Register  to view this content.

Questions? Does it work?
2.

[tomtomtom]

There might be (probably is) an easier way but here's what I would do.
1. Switch from HLOOKUP to INDEX so your original formula (assuming value in A1 and range in A3:M3) would be

Please Login or Register  to view this content.

2. We need to compare the difference between the 1 above and the one below that number. The value above can be found by

Please Login or Register  to view this content.

Sooooooo

Please Login or Register  to view this content.

Questions? Does it work?
2.

I tried this out and unfortunately I'm still having the same problem, it finds the closest value in the What If Scenario Summary, but only if it is lower than the inputted target profit. I put 14000 as a test target profit, since the what if scenario includes 14,002 as a possible profit, but with your formula it still came out with the closest value being 13,382.
Any ideas?

EDIT: I tried out the 2nd formula you posted and that actually works fine, cheers !

[ChemistB]

Upload a small example (xls format) of what you have with comments or sample cells on what you are trying to do and I'll see if I can match my formulas to your formatting.