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

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

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

Try

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

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

That's actually 4 different scenarios to test for then.

Originally Posted by legethi

- 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")

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.

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

jonmo1vlookup.xlsx

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

Glad to help, whatever works.