Index match not working for grater than value

[IrinaI]

Hi there!

I'm trying to make the index match formula work for -1 (greater than values) as I don't have the exact match for the value I'm looking for:
I have an invoice table and a tariff table and based on the value of the weight in the invoice I need it to get the cost.
If I use vlookup (image 1) it returns a value, but from the range less than the kg that I need--> it finds 342€ when it should be 411€ (image 2)
But then Index-match with -1 isn't working either... it returns NA (image 3)
Anyone have an idea of what could be happening?

Thanks!!

(1)

(2)

(3)

 

[arthurbr]

Try something like =INDEX(Tariffs!$A$5:$A$64;match(AE10;tariffs!$D$5:$D$64;1)+1)
(with the MATCH -1 switch lookup values must be sorted descending)

 

[IrinaI]

=INDEX(Tariffs!$A$5:$A$64;match(AE10;tariffs!$D$5:$D$64;1)+1)

Thanksss! The thing was I had them sort ascending, now it works!!! thanks a lot!

 

[Peter_SSs]

Try something like =INDEX(Tariffs!$A$5:$A$64;match(AE10;tariffs!$D$5:$D$64;1)+1)

Apart from I think the columns might be swapped in that compared to the OP's VLOOKUP, won't that give the wrong answer if the value being looked up exactly matches a value in the lookup table? (see column AG below)

@Irinal
Do you have the XLOOKUP function in your Excel 365 (not everybody does yet)? If so, that function has an option to look for next highest if no exact match.
It would go something like column AF below (with ; as your formula separator of course)

If you do not have XLOOKUP then an amended INDEX/MATCH is suggested in column AH that overcomes the issue I mentioned earlier.

Irinal 1.xlsm
	A	B	C	D
46	7001			1
47	8001			2
48	9500			3
49	10001			4
50	11000			5
Tariffs

Irinal 1.xlsm
	AE	AF	AG	AH
10	9486	3	3	3
11	9500	3	4	3
12	10200	5	5	5
Sheet2

Cell Formulas
Range	Formula
AF10:AF12	AF10	=XLOOKUP(AE10,Tariffs!$A$5:$A$64,Tariffs!$D$5:$D$64,"",1)
AG10:AG12	AG10	=INDEX(Tariffs!$D$5:$D$64,MATCH(AE10,Tariffs!$A$5:$A$64,1)+1)
AH10:AH12	AH10	=INDEX(Tariffs!$D$5:$D$64,MATCH(AE10,Tariffs!$A$5:$A$64,1)+(COUNTIF(Tariffs!$A$5:$A$64,AE10)=0))