# Formula compares a VALUE, to a column, and nearest match returns the adjacent column

• I want a formula (In K17 of attached SPREADSHEET), to return adjacent column, from opposite column NEAREST/CLOSEST value, to entered string.

This attached spreadsheet has working VLOOKUP formula that was suggested elsewhere, however I wanted a formula different to VLOOKUP, something I can edit more easily, hence any other suggestions welcome:

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

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

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.

Cheers Stephan

## Files

• Re: Formula compares a VALUE, to a column, and nearest match returns the adjacent col

The Formula =LOOKUP("Name", A1:A10, B1:B10) does the same thing as
=VLOOKUP("name", A1:B10, 2).

However =LOOKUP("name", A1:A10, B2:B11) will return the value one cell below that returned by the above formulas.

• Re: Formula compares a VALUE, to a column, and nearest match returns the adjacent col

Quote from mikerickson;591837

The Formula =LOOKUP("Name", A1:A10, B1:B10) does the same thing as
=VLOOKUP("name", A1:B10, 2).

However =LOOKUP("name", A1:A10, B2:B11) will return the value one cell below that returned by the above formulas.

MANY THANKS, exactly what I was looking for, the previous formula makes implied calculations of which I'm unfamiliar with, thanks for the more editable formula:

=LOOKUP(K8, F2:F159, G3:G159)

• Re: Formula compares a VALUE, to a column, and nearest match returns the adjacent col

Here is my latest edit, another DATABASE version, with QUICK START macro command buttons, all with no faults for SEARCH & ADD/EDIT/UPDATE/BROWSE, even includes photo preview in SEARCH & ADD/EDIT/BROWSE for records in database with photos inserted.

http://www.1sar.karoo.net/QUICKstartFRONTdb.xls 390 KB's

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!