Vlookup/Match problem

[WillM]

Hello,

I had a problem trying to set up a spreadsheet formula a while back and eventually got some help of this forum. I've used and adapted this formula for several things now and I can normally tweek it so I get the right answer !

I'm trying to do an analysis of payroll costs by cost centre and expense codes.

Basically I have a schedule that shows:

Cost Centre in Col C (approx 150 rows)

Expense Codes in Row 5 (there are about 10 of these)

What I want to do at intersection of Cost Centre+Expense is do a look up and have come up with following formula..

=IF(ISNA(VLOOKUP($C7,Pivot_Mth_Act,MATCH(E$5,Row4Act,0),0)),0,VLOOKUP($C7,Pivot_Mth_Act,MATCH(E$5,Row4Act,0),0))


Row4Act and Pivot_Mth_Act are range names on a pivot table. Row4Act looks up expense codes in a row on pivot, Pivot_Mth_Act is the whole pivot table (which has cost centre numbers in first column)

At the moment I'm getting a #VALUE! from my formula - any ideas where I've gone wrong ?

Thanks in advance..

Will M

 

[cornflakegirl]

#VALUE! is a more unusual error from VLOOKUP. Have you tried replacing the match bit with just a column number to test? And replacing the named ranges with standard ranges? Just to isolate which part of the formula is causing the error.

(The formula syntax looks fine to me, btw.)