formula

[jasman]

Hi. I have the below formula which works fine, but when i chnage the formula to include a range of cells to look at then it comes up with VALUE#

formula that works: ==IF('Q:\APC\Programmes\D7a\00_APC\001 - PPFeas Master\[D7a PPFeas Master.xls]Data Sheet'!$E$507=A2,'Q:\APC\Programmes\D7a\00_APC\001 - PPFeas Master\[D7a PPFeas Master.xls]Data Sheet'!$G$507)

formula that doesnt work but i need : =IF(Data Sheet'!$E$5:$E$1314=A2,Data Sheet'!$G$5:$G$1314)

 

[Andrew Poulsom]

That's an array formula so you would need to confirm it with Ctrl+Shift+Enter. But maybe you really want a lookup formula:

=IF(COUNTIF(Data Sheet'!$E$5:$E$1314,A2),VLOOKUP(A2,Data Sheet'!$E$5:$G$1314,3,FALSE),"")

 

[jasman]

none of them work either, same error

 

[Andrew Poulsom]

Sorry, missed some single quotes:

=IF(COUNTIF('Data Sheet'!$E$5:$E$1314,A2),VLOOKUP(A2,'Data Sheet'!$E$5:$G$1314,3,FALSE),"")

 

[jasman]

i included the quotes originally and it didnt work