When value isn't found with VLOOKUP - take next cell value

Hi guys,

I will try to lay out my problem and hope someone has a good idea :-)

I have a table with length (column A) (e.g. 2, 2.5, 3, 3.5 and so on) and the price for a length (column B).
I would like my formula to show the "next price" if it doesn't meet a criteria.

An example: I put in a value of 2.3 in a cell, and my formula is a simple VLOOKUP with "TRUE" parameter at the end, because else I get a fail.
This shows the price for 2.0, but I would like to show the price of 2.5 instead of 2.0.

Anyone can help me on this matter? Maybe I am using the wrong formula for this? :-)

Originally Posted by FDibbins

Try this...

	A	B	C	D
1	2	a	2.3	b
2	2.5	b
3	3	c
4	3.5	d

D1=INDEX($B$1:$B$4,MATCH(C1,A1:A4)+1)

Thank you so much, this almost solves the problem - only problem is, if I select 4.0, which has an exact price, it will show the "next price" instead of the price for 4.0 :-)

Originally Posted by hrlngrv

Problem is that =INDEX($B$1:$B$4,MATCH(C1,A1:A4)+1) would return b when C1 was 2. Looks like the OP wants exact matches when available.

The old school alternative, =INDEX(B$1:B$4,MATCH(C1,A$1:A$4)+(C1<>LOOKUP(C1,A$1:A$4))) .

However, OP shows office version as Office 365, so if that means s/he has XLOOKUP, =XLOOKUP(C1,A1:A4,B1:B4,,1) .

You guys are awesome! Thank you for the INDEX-formula editing, that made it work smoothly. I am able to make the XLOOKUP formula, but it returns the same value as the first INDEX formula I got from FDibbins.
It works - I am very grateful for your quick replies - Thanks a lot!

You might sort the table in opposite order and use VLOOKUP without fourth parameter.

XLOOKUP with 5th argument 1 should search for next largest. I works for me that way. I'd guess you believed the 2 consecutive commas were a typo and used 1 as 4th argument.