please consider the following formula, it returns a 'Yes' or 'No' from a Vlookup return depending if the record is a string 'True' or 'False'

=IF(VLOOKUP(A5,'Z:\Information Analysis\CSP Study Log Development\CSP Reporting\Excel Solution\[CSPProject.xls]Sheet1'!A:N,13,FALSE),"YES","NO")

However i have noticed that there are some blank cells in the data in which it is still returned as a 'No'. Can anyone please advise how to alter this to return a 'Not Assigned' if the cell is blank?

many thanks

Try this (untested):

=IF(VLOOKUP(A5,'Z:\Information Analysis\CSP Study Log Development\CSP Reporting\Excel Solution\[CSPProject.xls]Sheet1'!A:N,13,FALSE),"YES",IF(VLOOKUP(A5,'Z:\Info rmation Analysis\CSP Study Log Development\CSP Reporting\Excel Solution\[CSPProject.xls]Sheet1'!A:N,13,FALSE)="","Not Assigned","NO"))




It's quite a long formula so I'd be tempted to use a helper column instead.
In the helper column you could simply look up the value:
=VLOOKUP(A5,'Z:\Information Analysis\CSP Study Log Development\CSP Reporting\Excel Solution\[CSPProject.xls]Sheet1'!A:N,13,FALSE)

Then you can reference the helper column with the IF() conditions. Say the helper column is column B:
=IF(B5,"YES",IF(B5="","Not Assigned","NO"))



Also, I notice you are returning the value from the 13th column. If that's the case the lookup table can be reduced to A:M rather than A:N.

Hope that helps...

Hi Colin,
your first suggestion has the same outcome as my formula i.e if blank it returns a 'No'

I have tried the helper column example and the same applies since the returned value oin the helper column is either 'TRUE', 'FALSE' or '0', (the Vlookup returns a 0 from the blank cell)

the formula then interprets the 0 as a false thus returns a 'No'

thanks

hi Colin,
i just realised all i have to do is use the helper and make the formula look like this

=IF(B5,"YES",IF(B5=0,"Not Assigned","NO"))

Hi,

Sorry about that - I didn't test it and I didn't consider the coercion issue.

Yes, good work. Provided that column M contains boolean TRUE/FALSE values rather than string "TRUE"/"FALSE" values, an alternative might also be:

=IF(ISLOGICAL(B5),IF(B5,"YES","NO"),"Not Assigned")

Hi Colin,

actually both methods work, i realised i wasn't applying correct references to the first one,

apologies and many thanks for your help