Help with formula

Try the below, you need to put your condition outside then lookup brackets

=if(vlookup(b195,ITSPEND,2,0)="BLANKET",vlookup(b195,ITSPEND,3,0))

Is itspend a named range?
edit: I adjusted this from my phone but it should work

is this working

IT SPEND is a Named Range as thus =OFFSET('Total IT Spend'!$A$1,0,0,COUNTA('Total IT Spend'!$A:$A),5) which is a Pivot Table

Have you tried the new formulas?

PM1 said:

IT SPEND is a Named Range as thus =OFFSET('Total IT Spend'!$A$1,0,0,COUNTA('Total IT Spend'!$A:$A),5) which is a Pivot Table

Click to expand...

Why not invoke GETPIVOTDATA which is native to pivot tables?

Hi,

Just tried it and Yes it works

I get a FALSE if condition is not true but I'm sure I can work that out

Many Thanks

Now that is a big Why, still trying to work out the logic of GETPIVOT DATA

PM1 said:

Now that is a big Why, still trying to work out the logic of GETPIVOT DATA

Click to expand...

You should be able to find some examples even on this board...

What is supposed to be returned (with VLOOKUP or GETPIVOTDATA), text?

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.