Return adjacent column, from opposite column NEAREST/CLOSEST value, to entered string

[srands]

This question sounds simple enough, but eluded on internet searches so far!

I want a formula in K17, to return adjacent column, from opposite column NEAREST/CLOSEST value, to entered string.

Spreadsheet attached.

When value entered in K8 is met, then F2:F127 is searched for the nearest/closest single value, then the adjacent row in COLUMN G (G2:G127) is returned.

For example if K8 = 48
Then K17 = £0.13 (Also to complete the calc * K11 = £78.00)

Cheers

[TMS]

=VLOOKUP(K8,F:G,2)

Regards, TMS

[TMS]

Thanks for the rep.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[srands]

Many thanks, I have two other simple questions specific to the attached REVISED SPREADSHEET:

Q1). If I wanted a formula alternative OTHER then VLOOKUP, (Such as IF, COUNTIF or TRANSPOSE) what would the formula be?

Q2). The VLOOKUP formula returns ONE ROW before, when it should be the NEXT ROW. What would the corrected formula be?

For example, if K8 = 10 MPG,
then the COST per MILE (K17) should equal = £0.613 (Row 7),
however the value returned by the VLOOKUP formula is the previous row (£0.638 Row 6), this is for the row for 9.8 MPG the nearest to 10 MPG before going over 10 MPG. I guess to edit the VLOOKUP will be simple enough, but I'm not familiar with the VLOOKUP formulas.

Replies of suggestions/help, and writing on written FORMULAS (Hence not VB), were the code is seen and not implied, much appreciated.

Cheers

[TMS]

You said,

I want a formula in K17, to return adjacent column, from opposite column NEAREST/CLOSEST value, to entered string.

I believe that's what the formula gives you. The options you have with VLOOKUP are FALSE as the last parameter for an exact match or TRUE (or omitted) for the nearest below the value.

You could look at INDEX/MATCH as the alternative approach or just LOOKUP.

If you click on the fx option on the formula bar, it will list all the functions. If you select one, there is an option to get help on it with examples.


Regards, TMS

[srands]

MANY THANKS for all your help. Edited to SOLVED status!