VLOOKUP Formula -- maybe can combine with MATCH or change entirely

[Spinette]

The below formula works perfectly if the amount in cell G3 is exact. If, it is higher, but not as high as the next number, it chooses the lower, which I now understand is how that works with VLOOKUP. How can I alter the formula so that it chooses the next highest value if the amount in cell G3 is not exact?

=VLOOKUP(G3,'98 Title Ins Search'!A1:C490,2,FALSE)

[Sam Capricci]

If I understand you correctly change false to true but your reference data will need to be in ascending order.

[FDibbins]

Try this...

	A	B	C	D
2	2	a	5	c
3	4	b
4	6	c
5	8	d
6	10	e
7	12	f
8	14	g
9	16	h

D2=INDEX($B$2:$B$9,IFERROR(MATCH(C2,$A$2:$A$9,0),MATCH(C2,$A$2:$A$9)+1))

[Spinette]

Thanks, data is already in ascending order. Making it TRUE or FALSE makes no difference in outcome.

[Spinette]

Wait, correction. It does make a difference, but the difference I need is for it to move up to the next highest value versus the lower. For example it will make an exact match if cell G3 is 71,000, the search area will add the correct value I need for that number. But, if cell G3 is say 71,500 it will give me the same value as if at 71,000 and I want it to bump up to the value as if it were 72,000, which is the next highest number in the search area range. If that makes any sense at all.

[FDibbins]

Did you try my suggestion on your actual data?

[kvsrinivasamurthy]

Is it possible to have the column in descending order.

[kvsrinivasamurthy]

Pl see attached file. Example is worked out. Suitably change to your requirement.
Instead of Vlookup Use the Below formula

Please Login or Register  to view this content.

[Spinette]

I don't know how to do what you just did for what I need. I've attached the related file. The cell highlighted in red is myproblem. As you can see, if the number is exact it is fine. If the number is close it chooses the closest lowest and I want it to choose the next highest.

[kvsrinivasamurthy]

ARRAY formula in F21

Please Login or Register  to view this content.