VLOOKUP and MATCH based on Qty

[thewiseguy]

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

 

[maabadi]

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]

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]

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]

awesome! thank you so much.