INDEX MATCH with 1 conditions within a range

Dear all,

I am having a little Problem.

I need a vlookup function with 2 conditions but one of these conditions implies a result within a range.
In other words, I need to retrieve a Discount % from a list of suppliers However, this % will change depending on how much we purchases.
Let say supplier A will give you a 10% discount if you spend up to 10'000$, 20% if you spend between 10'001 and 20'000$ and 30% if you spend more than 20'001 and supplier B will give you a 10% discount if you spend up to 100'000$, 20% if you spend between 100'001 and 150'000$...

Now, let say I have, on another sheet, a list with all my suppliers with how much I spent for each one of them. I need something like an INDEX MATCH that will give me the discount that my supplier should give me. So let say I spent 15'000$ for supplier A, I should get a 20% discount. I could do something "manually" but since I have quite an important list, I need a formula.

Can anyone help me?

PLEAAAAAASE

Thank you.

Please post a small sample file (without confidential information) showing the calculation(s) you require.

Please Login or Register  to view this content.

One way: set tables (Named ranges) for each supplier .

Then use VLOOKUP

=VLOOKUP($I$1,Supplier_A,2,1) where $I$1 is spend with Supplier A

or

=VLOOKUP($I$1,INDIRECT(H1),2,1)

where H1 is named range

For A if purchase is 25000, what is the discount % and how total discount is calculated.

See attached:

I defined a named range for each Supplier e.g Supplier_A refers to: B1:C3

I then used the following formula to get discount:

=VLOOKUP(H1,INDIRECT("Supplier_"&G1),2,1)

G1= "A", H1=25000 (from your example)

I think your table (using "A" as an example) should look like this:

Please Login or Register  to view this content.

so value up to and including 10000 will get 3%, 10001 -20000 5% and over 20001 4%

Try this D6=Person, E6=Amount
ARRAY formula
=SMALL(IF(($A$2:$A$11=D6)*($B$2:$B$11>=E6),$C$2:$C$11,""),1)

Thank you both for your help. Both formulas work great. You guys saved me a lot of work.

I just have a remark regarding John's formula. Indeed, the formula works great but naming ranges for each supplier quickly becomes a lot of work. Thus, I think I am gonna go for ksrinivasamurthy's formula. Thanks again.

Gerald