+ Reply to Thread
Results 1 to 11 of 11

How to with Vlookup perform a test on 3 condition (EMPTY, Not Existing and correct Value)

  1. #1
    Registered User
    Join Date
    06-06-2013
    Location
    Geneva
    MS-Off Ver
    Excel 2010
    Posts
    10

    Post How to with Vlookup perform a test on 3 condition (EMPTY, Not Existing and correct Value)

    Hi all,

    I want to do a consolidation between to Excel Sheet having information coming from 2 different place.

    My goal is based on a value from the sheet named AvayaSiebel to perform a vlookup from the sheet named TestimanagedDevice and based on the result, write in the result cell Blank if row exist but without value in the cell, not found if row doesn't exist and value in case where the row exist and there is a value in the concern cell instead of having the #N/A result is cell is blank or row doesn't exist
    If I use simple (VLOOKUP(A2;testimanagedDevices!$C$2:$H$91;2;FALSE))

    I'm trying to apply the following formula: =IF(ISBLANK(VLOOKUP(C2;testimanagedDevices!$B$2:$C$91;2;FALSE))="-";"NotFound";(VLOOKUP(C2;testimanagedDevices!$B$2:$C$91;2;FALSE)))

    I have try also the following formula:
    =IF(ISBLANK(VLOOKUP(C2;testimanagedDevices!$B$2:$C$9;2;FALSE));"-";IF(ISERROR(VLOOKUP(C2;testimanagedDevices!$B$2:$C$9;2;FALSE));"Not found";VLOOKUP(C2;testimanagedDevices!$B$2:$C$9;2;FALSE)))
    But without no luck
    I don't know if this kind of test is possible with formula or if a macro is necessary to do this. If macro, it will be nice to help me on the script as I'm a real novice on Excel.
    Thanks in advance for your help.

    Thibaut
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    06-06-2013
    Location
    Geneva
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: How to with Vlookup perform a test on 3 condition (EMPTY, Not Existing and correct Val

    Looking on some other discussion, I have found on the following Discussion http://www.excelforum.com/tips-and-t...ue-exists.html a start of tip.

    My formulas now is as the follow
    =IF(ISNA(VLOOKUP(C2;testimanagedDevices!$B$2:$C$91;2;FALSE));C2&" Not Found";(VLOOKUP(C2;testimanagedDevices!$B$2:$C$91;2;FALSE)))

    This provide me the following result:

    (628)379-4941
    5000059084 Not Found
    (628)379-4940
    Null Not Found

    Now what could be the best way to change when I have "Null Not Found" by something like Empty
    Thanks for the help in advance.
    Kind Regards
    Thibaut
    Attached Files Attached Files
    Last edited by legethi; 06-11-2013 at 05:30 PM.

  3. #3
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: How to with Vlookup perform a test on 3 condition (EMPTY, Not Existing and correct Val

    Hi legethi

    If you are just referring to column O, change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    Now whilst trying not to cause offence, that seemed a little easy. Have I missed something?

    Regards
    Alastair

  4. #4
    Registered User
    Join Date
    06-06-2013
    Location
    Geneva
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: How to with Vlookup perform a test on 3 condition (EMPTY, Not Existing and correct Val

    Hi Alastair,
    Thanks for your answer.
    If it was this, you are right it will be really easy but not the case.
    What I need is to based on my test 3 possible answer as following scenario:

    - If the Value in my column C3 exist in the sheet testimanagedDevice column B, I want to show in the column O the value of the column C from the sheet testimanagedDevice.
    - If the Value in my column C3 exist in the sheet testimanagedDevice column B, but Column C from testimanagedDevice column sheet is empty, I want to show in the column O the value "Empty".
    - If the Value in my column C3 Does not exist in the sheet testimanagedDevice column B, I want to show in the column O the value "Do not exist".

    Let me know if you need more detail:
    Kind Regards
    Legethi

  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: How to with Vlookup perform a test on 3 condition (EMPTY, Not Existing and correct Val

    Try

    =IFERROR(IF(VLOOKUP(...)=0,"Empty",VLOOKUP(...)),"Does Not Exist")

  6. #6
    Registered User
    Join Date
    06-06-2013
    Location
    Geneva
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: How to with Vlookup perform a test on 3 condition (EMPTY, Not Existing and correct Val

    Hi Jonmo1,

    Thanks for the answer.
    Based on your proposal, I have write this formula

    =IFERROR(IF(VLOOKUP(C2;testimanagedDevices!$B$2:$C$91;2;FALSE)=0;"Empty";(VLOOKUP(C2;testimanagedDevices!$B$2:$C$91;2;FALSE)));"Does Not Exist")

    But this show the Does not exist in the both case where the value is empty or do not exist.
    Thanks for your help.
    Legethi

  7. #7
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: How to with Vlookup perform a test on 3 condition (EMPTY, Not Existing and correct Val

    That's actually 4 different scenarios to test for then.
    Quote Originally Posted by legethi View Post
    - If the Value in my column C3 exist in the sheet testimanagedDevice column B, I want to show in the column O the value of the column C from the sheet testimanagedDevice.
    - If the Value in my column C3 exist in the sheet testimanagedDevice column B, but Column C from testimanagedDevice column sheet is empty, I want to show in the column O the value "Empty".
    - If the Value in my column C3 Does not exist in the sheet testimanagedDevice column B, I want to show in the column O the value "Do not exist".

    AND NOW, If C2 is Blank, I want to show in the column O the value "Empty".
    Try

    =IFERROR(IF(C2="","Empty",IF(VLOOKUP(...)=0,"Empty",VLOOKUP(...))),"Does Not Exist")

  8. #8
    Registered User
    Join Date
    06-06-2013
    Location
    Geneva
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: How to with Vlookup perform a test on 3 condition (EMPTY, Not Existing and correct Val

    Hi Jonmo1,

    This is not ok, as I write:
    "- If the Value in my column C2 exist in the sheet testimanagedDevice column B, but Column C from testimanagedDevice column sheet is empty, I want to show in the column O the value "Empty"."

    This mean that Column C will still have a value but The Value in column C, which is link to my Column B, from testimanagedDevice is Empty.
    this point to me that I forgot the my Column C that I use as reference is in a sheet call AvayaSiebel, the same sheet where I have my column o.

  9. #9
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: How to with Vlookup perform a test on 3 condition (EMPTY, Not Existing and correct Val

    I don't know what to tell you, it works for me.

    jonmo1vlookup.xlsx

  10. #10
    Registered User
    Join Date
    06-06-2013
    Location
    Geneva
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: How to with Vlookup perform a test on 3 condition (EMPTY, Not Existing and correct Val

    Hi Jonmo,

    Thanks, Effectively this work, issue come from my Excel version where there is another option which can be in C5, value is Null. So I have update based on your good Formula to this way:

    =IFERROR(IF(C2="";"Empty";IF(C2="Null";"Empty";IF(VLOOKUP(C2;testimanagedDevices!$B$2:$C$91;2;FALSE)=0;"Empty";VLOOKUP(C2;testimanagedDevices!$B$2:$C$91;2;FALSE))));"Does Not Exist")

    Thanks very much for the help

  11. #11
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: How to with Vlookup perform a test on 3 condition (EMPTY, Not Existing and correct Val

    Glad to help, whatever works.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1