Indirect Vlookup with IF statements

[thewiseguy]

Hi all. Having trouble combining functions.

Here is my formula which needs some work:

=IFERROR(VLOOKUP(B2,INDIRECT(VLOOKUP(C2,TableNames,2)),8,0),"")

instead of returning the value in the 8th column, I want to add these IF conditions:

=IF(E2≤20,17)
=IF(E2 is between 21-50, 18)
=IF(E2 is between 51-100, 19)
=IF(E2 ≥ 100,20)

is this possible?

many thanks in advance.

 

[Fluff]

Replace the 8 with
16+MATCH(E2,{0,21,51,101},1)

 

[thewiseguy]

Replace the 8 with
16+MATCH(E2,{0,21,51,101},1)

sorry, I completely gave the wrong information. In my above example where you see, 17,18,19,20 - these represent the column numbers to lookup in the vlookup

 

[Fluff]

In that case please post some sample data using the XL2BB add-in, showing expected results & why.
Also please update you account details, to show what version of Excel you are using.

 

[thewiseguy]

This my BOM tab (bill of materials). It will have any number of items in the "B" column.

Proposal5.xlsm
	A	B	C	D	E	F
1	Manufacturer	Item	FixtureCategory	Product ID	Qty	Cost
2	T-1	T-1 Lighting 135W LED Linear High Bay w/ Occ. Sensor 5K	HighBayLinear	T1-G2HBLED/U/135-50-OS	108
Sheet1

I am trying to get the cost of the item in column B, based on the qty (Column E) - (prices goes down as qty goes up).

On multiple separate tabs, I have tables. My indirect with vlookup formula that I mentioned above, is the formula I am trying to use.

The value in this example that should be returned would be $104

Proposal5.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	EMA DESCRIPTION	DESCRIPTION	ECM Category	ECM Spec.	Replaces	ECM Size	MFG	PART NO.	ECM Lumens	NO. LAMPS	LAMP WATTS	NO. FIXTURES	FIXTURE WATTS	SYSTEM WATTS	ECM LIFE	ECM TEMP	Lamp Cost	1-20PC	21-50PC	51-100PC	101-200PC	TOTAL ECM COST	LABOR	ECM UNIT INCENTIVE	MEASURE CODE	NJSS Proposed Fix Type	NJSS Fix/Lamp Desc.	N/R	Notes
2	T-1 Lighting 321W LED Linear High Bay 4K	321W LED Linear High Bay 4K	HighBay	Linear		2FT	T-1 Lighting	T1-HBLED/B/321-40				1	321	321	50000	4000		$259.00	$234.00	$227.00	$220.00		30	150	PL11	LED - DLC Fixture	LED High-Bay and Low-Bay Fixtures >250W	N
3	T-1 Lighting 321W LED Linear High Bay 5K	321W LED Linear High Bay 5K	HighBay	Linear		2FT	T-1 Lighting	T1-HBLED/B/321-50				1	321	321	50000	5000		$259.00	$234.00	$227.00	$220.00		30	150	PL11	LED - DLC Fixture	LED High-Bay and Low-Bay Fixtures >250W	N
4	T-1 Lighting 135W LED Linear High Bay w/ Occ. Sensor 4K	135W LED Linear High Bay w/ Occ. Sensor 4K	HighBay	Linear		2FT	T-1 Lighting	T1-G2HBLED/U/135-40-OS				1	135	135	50000	4000		$111.00	$109.00	$106.00	$104.00		30	75	PL10	LED - DLC Fixture	LED High-Bay and Low-Bay Fixtures >125W to ≤250W	N
5	T-1 Lighting 135W LED Linear High Bay w/ Occ. Sensor 5K	135W LED Linear High Bay w/ Occ. Sensor 5K	HighBay	Linear		2FT	T-1 Lighting	T1-G2HBLED/U/135-50-OS				1	135	135	50000	5000		$111.00	$109.00	$106.00	$104.00		30	75	PL10	LED - DLC Fixture	LED High-Bay and Low-Bay Fixtures >125W to ≤250W	N
Sheet4

 

[Fluff]

Assuming that you are looking up the value in col B of sheet4, then just replace the 8 in your formula with the formula I supplied.

 

[thewiseguy]

Assuming that you are looking up the value in col B of sheet4, then just replace the 8 in your formula with the formula I supplied.

so it looks like the formula is not accounting for quantities from 1-20.

also, the formula works for quantities from 21-200, however it is returning the value from the column left of where it should be.

 

[Fluff]

If E2 <21 it will pull data from the 17 column of the lookup range, which is what you said you wanted.

 

[thewiseguy]

so it looks like the formula is not accounting for quantities from 1-20.

also, the formula works for quantities from 21-200, however it is returning the value from the column left of where it should be.

*edit - i changed the 16 to 17 and it is now working.

i can't thank you enough - that is extremely helpful and impressive.

thank you!

 

[Fluff]

You're welcome & thanks for the feedback.