Nested IF/OR/VLOOKUP

[DJEinConcord]

Nested IF/OR/VLOOKUP
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Hello Forum,
<o> </o>
New member, seasoned Excel user with a fairly hard one for the boards.
<o> </o>
What I am trying to do is run a check on a returned spreadsheet I sent to users for them to fill out. First check is to see if the users filled in the cell with anything, and second check is if what they filled in is a valid selection from a named range of data (CatApprovLevl).
<o> </o>
In English I am trying to see if the value in cell D14 is 0, if it is I want a return value of “Blank” letting me know the user did not enter any data at all.
<o> </o>
Then if they did enter something I want to check what they entered was a valid entry by checking that data against a range(CatApprovLevl). If it is in the range, I want a returned value of “OK.”
<o> </o>
If they entered a value not in my range (CatApprovLevl) I want a returned value of “Error.”
<o> </o>
I am getting the dreaded #N/A instead of the expected “ERROR” value.
<o> </o>
Here is the faulty code:
<o> </o>
=IF(D14=0,"Blank",IF(OR(D14=VLOOKUP(D14,CatApprovLevl,1,FALSE)),"OK","Error"))
<o> </o>
<o> </o>
Assumptions:
<o> </o>
The range of data is valid and correct entries are judges against it and return “OK” values.
<o> </o>
It is the bad entries, not in the list, that give the cursed #N/A .
<o> </o>
Thanks for looking!

DJE

 

[Aladin Akyurek]

Try...

=IF(D14="","Blank",IF(ISNUMBER(MATCH(D14,INDEX(CatApprovLevl,0,1),0)),"OK","Error"))

 

[DJEinConcord]

Thanks Aladin.

It returned all ERRORS and BLANKS, but no OKs. There are lots of OK entries.

The values int the named range are text. Would that matter with the ISNUMBER function?

 

[Aladin Akyurek]

Thanks Aladin.

It returned all ERRORS and BLANKS, but no OKs. There are lots of OK entries.

The values int the named range are text. Would that matter with the ISNUMBER function?

IsNumber is not the problem for it checks whether Match returns a number. The Match function returns an integer (number) when successful, otherwise #N/A.

Does...

=IF(D14="","Blank",IF(ISNUMBER(MATCH(TRIM(D14),INDEX(CatApprovLevl,0,1),0)),"OK","Error"))

succeed?

 

[DJEinConcord]

Thank you!

This one wokred:

=IF(D14="","Blank",IF(ISNUMBER(MATCH(D14,INDEX(CatApprovLevl,0,1),0)),"OK","Error"))

I must have done something wrong when I first tried it. It works great!

Thanks again.