# VLOOKUP and MATCH based on Qty

#### thewiseguy

##### Well-known Member
Hi all,

I'm looking for a VLOOKUP formula which based on cell criteria (quantity), returns a specific column. I am essentially looking up a product quantity amount and returning the price to buy it at that quantity.

My Lookup value = B2
My Table Array = TableProposed
My Column references for quantity in TableProposed = 18,19,20,20

Any help is greatly appreciated!

/M

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### thewiseguy

##### Well-known Member
This is the active sheet. The Item column is looked up in "ProposedTable (below) and the Qty column is referenced to see what range it falls between (in the table below; 1-20, 21-50, 51-100, 101-200.

Hoping this is enough information.

Thank you.

 Manufacturer Item Product ID Qty Std. Cost ASD ASD 22W LED Flat Panel 2x2 MV 4K Recessed ASD-ELP01-22D2240-PRM 102 \$4.00

 Reference IndoorOutdoor Category SubCategory Voltage Temp Mount Backup Sensor MFG Name PART NO. OPTIONS NO. SYSTEM WATTS NJSS Watts ECM LIFE 1-20PC 21-50PC 51-100PC 101-200PC ASD 22W LED Flat Panel 2x2 MV 4K Recessed Indoor Flat Panel 2x2 MV 4K Recessed ASD ASD 22W LED Flat Panel 2x2 MV 4K Recessed ASD-ELP01-22D2240-PRM 22 22 81000 \$1.00 \$2.00 \$3.00 \$4.00 ASD 27W LED Flat Panel 2x2 MV 4K Recessed Indoor Flat Panel 2x2 MV 4K Recessed ASD ASD 27W LED Flat Panel 2x2 MV 4K Recessed ASD-ELP02-22D2740-STD 27 27 81000 \$36.25 \$36.25 \$36.25 \$36.25 ASD 40W LED Flat Panel 2x4 MV 4K Recessed BB Indoor Flat Panel 2x4 MV 4K Recessed BB ASD ASD 40W LED Flat Panel 2x4 MV 4K Recessed BB ASD-ELP12-24D4040-SHE 40 40 81000 \$180.00 \$180.00 \$180.00 \$180.00

#### alz

##### Board Regular
Hi Try This.!

=VLOOKUP(\$B\$2,TableProposed,IF(\$D\$2<=20,17,IF(\$D\$2<=50,18,IF(\$D\$2<=100,19,20))),0)

#### thewiseguy

##### Well-known Member
awesome! thank you so much.

alz

Replies
1
Views
163
Replies
4
Views
104
Replies
2
Views
479
Replies
6
Views
186
Replies
6
Views
135

1,141,704
Messages
5,707,970
Members
421,539
Latest member
zuniBM

### 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