Rate fetching from rate card with more than 1 condition

[Sandeep1295]

Transaction Data
Sheet Name	Txn Value	Rate			Rate Card Table
1	15000	?			Scheme	Lower Vale	Upper Value	Rate
2	13000	?			1	0	12999	2
3	20000	?			1	13000	19999	4
					1	20000		5
					2	0	6999	1
					2	7000	11999	2
					2	12000		3
					3	0	14999	4
					3	15000	24999	5
					3	25000		6

given above is short data and actual data have huge no of line items require formula for sort this out

[#2 · Accepted solution]

@Sandeep1295 

=INDEX($I$4:$I$12,MATCH(1,($F$4:$F$12=A3)*($G$4:$G$12<=B3)*($H$4:$H$12>=B3),0))

Maybe with this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

[#3]

It works. Thank you very much @OliverScheurich