Finding the Nearest Value in a What-If Scenario

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

==INDEX(A3:M3,1,MATCH(A1,A3:M3,1))

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

=INDEX(A3:M3,1,MATCH(A1,A3:M3,1)+1)

Sooooooo

IF(A1-INDEX(A3:M3,1,MATCH(A1,A3:M3,1))>=INDEX(A3:M3,1,MATCH(A1,A3:M3,1)+1)-A1,=INDEX(A3:M3,1,MATCH(A1,A3:M3,1)),=INDEX(A3:M3,1,MATCH(A1,A3:M3,1)+1))

Questions? Does it work?
2.

Originally Posted by 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

==INDEX(A3:M3,1,MATCH(A1,A3:M3,1))

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

=INDEX(A3:M3,1,MATCH(A1,A3:M3,1)+1)

Sooooooo

IF(A1-INDEX(A3:M3,1,MATCH(A1,A3:M3,1))>=INDEX(A3:M3,1,MATCH(A1,A3:M3,1)+1)-A1,=INDEX(A3:M3,1,MATCH(A1,A3:M3,1)),=INDEX(A3:M3,1,MATCH(A1,A3:M3,1)+1))

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 !

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.