# VLookup with criteria

##### New Member
Hello all

I have below table. Now

1-5 \$12000
6-10 \$11000
11-20 \$10000
20 or higher \$9000

How to solve this problem with Vlookup?

Can any one help me on this?

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

The easiest way to do that would be to create a 20x3 table where:
Column 1 is the add count from 1 to 20
Column 2 is the cost of the add for that count
Column 3 is a running total of Column 2

So with that, let's assume you create a table in range A1:C20 that looks like this:
Code:
``````1	12000	12000
2	12000	24000
3	12000	36000
4	12000	48000
5	12000	60000
6	11000	71000
7	11000	82000
8	11000	93000
9	11000	104000
10	11000	115000
11	10000	125000
12	10000	135000
13	10000	145000
14	10000	155000
15	10000	165000
16	10000	175000
17	10000	185000
18	10000	195000
19	10000	205000
20	9000	214000``````
Assuming cell E1 contains the number of adds purchased:
=VLOOKUP(E1,A1:C20,3)+VLOOKUP(E1,A1:C20,2)*MAX(E1-20,0)

There may be fancier array formula solutions... Personally, I like the layout and simplicity of this construct. Usually tiered pricing structures change from time-to-time and this layout is very easy to maintain, update, and audit. If we were dealing with price-quantity discounts for orders numbering in the thousands, obviously we might want to rethink our pricing table structure a bit.

Last edited:

Ah well, you know what I meant

Does this alternative....

=SUMPRODUCT(--(F2>{0;5;10;20}), (F2-{0;5;10;20}),{12000;-1000;-1000;-1000})

satisfy, where F2 houses the number of ads like 8?

Replies
5
Views
132
Replies
1
Views
556
Replies
1
Views
873
Replies
6
Views
375
Replies
2
Views
36

1,196,480
Messages
6,015,450
Members
441,896
Latest member
clomah

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back