# 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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

##### Well-known Member
Please Upload Example file & Desired Results with XL2BB ADDIN(Preferable) or upload at free hosting Site e.g. www.dropbox.com , GoogleDrive or OneDrive & insert Link here.

#### 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
129
Replies
2
Views
378
Replies
6
Views
174
Replies
6
Views
112
Replies
5
Views
353

1,136,908
Messages
5,678,512
Members
419,768
Latest member
eguechi09x

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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