VLOOKUP and MATCH based on Qty

thewiseguy

Well-known Member
Joined
May 23, 2005
Messages
818
Office Version
  1. 365
Platform
  1. Windows
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.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,966
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Joined
May 23, 2005
Messages
818
Office Version
  1. 365
Platform
  1. Windows
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.

ManufacturerItemProduct IDQtyStd. Cost
ASDASD 22W LED Flat Panel 2x2 MV 4K Recessed ASD-ELP01-22D2240-PRM102$4.00




ReferenceIndoorOutdoorCategorySubCategoryVoltageTempMountBackupSensorMFGNamePART NO.OPTIONS NO.SYSTEM WATTSNJSS WattsECM LIFE1-20PC21-50PC51-100PC101-200PC
ASD 22W LED Flat Panel 2x2 MV 4K Recessed IndoorFlat Panel2x2MV4KRecessedASDASD 22W LED Flat Panel 2x2 MV 4K Recessed ASD-ELP01-22D2240-PRM222281000$1.00$2.00$3.00$4.00
ASD 27W LED Flat Panel 2x2 MV 4K Recessed IndoorFlat Panel2x2MV4KRecessedASDASD 27W LED Flat Panel 2x2 MV 4K Recessed ASD-ELP02-22D2740-STD272781000$36.25$36.25$36.25$36.25
ASD 40W LED Flat Panel 2x4 MV 4K Recessed BB IndoorFlat Panel2x4MV4KRecessedBBASDASD 40W LED Flat Panel 2x4 MV 4K Recessed BB ASD-ELP12-24D4040-SHE404081000$180.00$180.00$180.00$180.00
 

alz

Board Regular
Joined
Jul 17, 2020
Messages
130
Office Version
  1. 2019
Platform
  1. Windows
Hi Try This.!

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

Forum statistics

Threads
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?

Disable AdBlock

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
Top