SOLVED

# Rate fetching from rate card with more than 1 condition

Copper Contributor

# Rate fetching from rate card with more than 1 condition

 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 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Rate fetching from rate card with more than 1 condition

``=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.

# Re: Rate fetching from rate card with more than 1 condition

It works. Thank you very much @OliverScheurich

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Rate fetching from rate card with more than 1 condition

``=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.