Results 1 to 6 of 6

Thread: Vlookup and if

  1. #1
    Join Date
    Apr 2006
    Posts
    65

    Unanswered: Vlookup and if

    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

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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...

  3. #3
    Join Date
    Apr 2006
    Posts
    65
    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

  4. #4
    Join Date
    Apr 2006
    Posts
    65
    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"))

  5. #5
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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")

  6. #6
    Join Date
    Apr 2006
    Posts
    65
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •