I want a formula (In K17 of this SPREADSHEET), to return adjacent column, from opposite column NEAREST/CLOSEST value, to entered string.
Spreadsheet link
http://www.excelforum.com/attachment.php?attachmentid=136789&d=1326823678
This spreadsheet has working VLOOKUP formula that was suggested elsewhere, however I wanted a formula different to VLOOKUP, something I can get to grips with more easily, hence any other suggestions welcome:
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.
Cheers Stephan
CROSSTHREAD HYPERLINKS:
http://www.excelforum.com/excel-gen...losest-value-to-entered-string.html?p=2685086
Spreadsheet link
http://www.excelforum.com/attachment.php?attachmentid=136789&d=1326823678
This spreadsheet has working VLOOKUP formula that was suggested elsewhere, however I wanted a formula different to VLOOKUP, something I can get to grips with more easily, hence any other suggestions welcome:
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.
Cheers Stephan
CROSSTHREAD HYPERLINKS:
http://www.excelforum.com/excel-gen...losest-value-to-entered-string.html?p=2685086