Help with formula

[PM1]

Hi Guys,

Struggling with a If Vlookup Statement.

I have =if(vlookup(b195,ITSPEND,2,="BLANKET",vlookup(b195,ITSPEND,3,0)) but theres an error and I can't see it

 

[Aladin Akyurek]

I have one sheet which is full of Purchase Order Data. From that I constucted a Pivot Table that just reports the Budget Line, PO No & Spend Year.

I used the Offset Formula as the table is continously growing.

In another sheet I have all the Budjet Data and so I have added more columns of which one is the current Purchase Order Spend against the Budget. However I had to construct another Column to show the value of the Purchase Order where the Supplier is BLANKET, the reason for BLANKET is to show a spend against a budget where a supplier has not yet been chosen.

I now have 2 columns, one will have the total spend from the pivot table where BLANKET is not met, and the other where it is met. I then have another Column for Invoiced amount and finally an Accrual Column.

That in a nut shell is it. Getting the basic data is not a problem, it is a problem when I needed to meet 2 consitions in a pivot table, and of course deal with the issues of data not being found or N/A being returned.

What are those two conditions, which ranges must these conditions meet, what is the range from which a result must be returned, and what kind of result gets returned - text or number? You can formulate the answers in terms of ITSPEND...

 

[PM1]

In Blanket Column I clearly state that if The Budget Line Code (B2) is found in the Pivot Table and in the Supplier Field "Blanket" is also present then give me the result, if the condition is not met then return a 0. This is because in the other column I do the same condition but without the Supplier Field being met, so a simple VLookup.

Excel Workbook
	A	B	C	D
3	Sum of Ext Cost USD		Years
4	IT Line Code	Supplier	2013	2014
5	HM016	BLANKET		$ 6,894
6		PACC / MDS	$ 4,084
7	HM016 Total		$ 4,084	$ 6,894
8	Grand Total		$ 4,084	$ 6,894
9
Total IT Spend

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	T	U	V	W	X	Y
1	CAPEX / OPEX	Item Line Code	Line Description	2013F Local Currency	Local Cur	2013F US$	2013F EURO	2014B Local Currency	Local Cur	2014B US$	2014B EURO	OLD Item Line Code		Budget 2014 USD	Blanket Order	LPO Raised	Invoiced 2014 USD	Accrual USD	Variance Against Budget USD
97	OPEX	HM016	SERVER MAINTENANCE - AUH	6,894	USD	6,894	5,225	6,894	USD	6,894	5,225	HM016		6,894	6894	0	0	-6,894	-6,894
IT Budget as at Nov 13

 

[Aladin Akyurek]

In Blanket Column I clearly state that if The Budget Line Code (B2) is found in the Pivot Table and in the Supplier Field "Blanket" is also present then give me the result, if the condition is not met then return a 0. This is because in the other column I do the same condition but without the Supplier Field being met, so a simple VLookup...

There is a probably a good reason why the answers are not concise...

The two conditions are still not formulated in a straightforward manner. Nevertheless, venturing a guess:

Control+shift+enter, not just enter...

=IFERROR(INDEX(ResultRange,MATCH(1,IF(SupplierRange="Blanket",If(I-range=B2,1)),0)),0)

Does this help at all?

 

[PM1]

There is a probably a good reason why the answers are not concise...

The two conditions are still not formulated in a straightforward manner. Nevertheless, venturing a guess:

Control+shift+enter, not just enter...

=IFERROR(INDEX(ResultRange,MATCH(1,IF(SupplierRange="Blanket",If(I-range=B2,1)),0)),0)

Does this help at all?

It looks better

However it returned 0 instead of 6894

Pivot Table shows:-

Excel Workbook
	A	B	C	D
3	Sum of Ext Cost USD	*	Years	*
4	IT Line Code	Supplier	2013	2014
5	HM016	BLANKET	*	$ *6,894
6	*	PACC / MDS	$ *4,084	*
7	HM016 Total	*	$ *4,084	$ *6,894
8	Grand Total	*	$ *4,084	$ *6,894
Total IT Spend

And result sheet shows

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	T	U	V	W	X
1	CAPEX / OPEX	Item Line Code	Line Description	2013F Local Currency	Local Cur	2013F US$	2013F EURO	2014B Local Currency	Local Cur	2014B US$	2014B EURO	OLD Item Line Code	*	Budget 2014 USD	Blanket *Order	LPO Raised	Invoiced 2014 USD	Accrual USD
97	OPEX	HM016	SERVER MAINTENANCE - AUH	* * * * 6,894	USD	* * * * * *6,894	* * * * * * * 5,225	* * * * 6,894	USD	* * * * * *6,894	* * * * *5,225	HM016	*	* * * * * * * * * * * * * * * 6,894	0	6,894	0	-6,894
IT Budget as at Nov 13

 

[Aladin Akyurek]

It looks better

However it returned 0 instead of 6894

Pivot Table shows:-

Total IT Spend

*	A	B	C	D
3	Sum of Ext Cost USD	*	Years	*
4	IT Line Code	Supplier	2013	2014
5	HM016	BLANKET	*	$ *6,894
6	*	PACC / MDS	$ *4,084	*
7	HM016 Total	*	$ *4,084	$ *6,894
8	Grand Total	*	$ *4,084	$ *6,894

<tbody>
</tbody>

...

Is this from where a result must be fetched with as conditions (1) IT Line Code = HM016 and Supplier = BLANKET, yielding the result of 6,894?

 

[PM1]

Yes, correct.

 

[Aladin Akyurek]

Is this from where a result must be fetched with as conditions (1) IT Line Code = HM016 and Supplier = BLANKET, yielding the result of 6,894?

Yes, correct.

=IFERROR(LOOKUP(9.99999999999999E+307,INDEX(A5:D8,MATCH(1,IF(A5:A8="HM016",IF(B5:B8="BLANKET",1)),0),0)),"")

which must be confirmed with control+shift+enter, not just enter.

 

[PM1]

=IFERROR(LOOKUP(9.99999999999999E+307,INDEX(A5:D8,MATCH(1,IF(A5:A8="HM016",IF(B5:B8="BLANKET",1)),0),0)),"")

which must be confirmed with control+shift+enter, not just enter.

Aladin, this works for a single incident, ie HM016, but I have many different Project Codes in the Pivot Table which continually grows.In my VLOOKUP's I created a Range in the Pivot Table called IT SPEND which is made of =OFFSET('Total IT Spend'!$A$1,0,0,COUNTA('Total IT Spend'!$A:$A),5), so editing the formula above I constructed

=IFERROR(LOOKUP(9.99999999999999E+307,INDEX(ITSPEND,MATCH(1,IF(ITSPEND='IT Budget as at Nov 13'!B97,IF(ITSPEND="BLANKET",1)),0),0)),"")

I substituted if(A5:A8="HM016" with B97 which is the location of ITSPEND='IT Budget as at Nov 13'!B97 as that is where the referenced cell is.

Anyway it doesnt work , just returns Blank

 

[Aladin Akyurek]

Aladin, this works for a single incident, ie HM016, but I have many different Project Codes in the Pivot Table which continually grows.In my VLOOKUP's I created a Range in the Pivot Table called IT SPEND which is made of =OFFSET('Total IT Spend'!$A$1,0,0,COUNTA('Total IT Spend'!$A:$A),5), so editing the formula above I constructed

=IFERROR(LOOKUP(9.99999999999999E+307,INDEX(ITSPEND,MATCH(1,IF(ITSPEND='IT Budget as at Nov 13'!B97,IF(ITSPEND="BLANKET",1)),0),0)),"")

I substituted if(A5:A8="HM016" with B97 which is the location of ITSPEND='IT Budget as at Nov 13'!B97 as that is where the referenced cell is.

Anyway it doesnt work , just returns Blank

If we want to refer to the dynamic named range ITSPEND, we must pick out the right parts from it...

=IFERROR(LOOKUP(9.99999999999999E+307,INDEX(ITSPEND,MATCH(1,IF(INDEX(ITSPEND,0,1)='IT Budget as at Nov 13'!B97,IF(INDEX(ITSPEND,0,2)="BLANKET",1)),0),0)),"")

which is still to be confirmed with control+shift+enter, not just enter.

 

[bradhog]

I was too getting the same problem. I tried this formula=if(vlookup(b195,ITSPEND,2,0)="BLANKET",vlookup(b195,ITSPEND,3,0))and this really helped me out.Thank u and keep updating with such formulae.