lookup function not returning correct value

[snsyg]

hello,

i am trying to search a range of cells for a certain value,
specifically an "X".

from my understanding, in order for the lookup function to work
properly, the list must first be sorted and in proper order...that is
not possible for what i am doing.

as a result of using an unsorted range of cells, some times an
incorrect value is returned.

here is the formula i am using:

=IF(ISNA(LOOKUP("X",A11)),"OK",(IF(LOOKUP("X",A11)<>"X","OK","Not
Complete")))

my intent is that anytime there is at least one "X" in the range of
cells, the value that is returned is "Not Complete", regardless of
whatever other values are in the cells before or after it.

if i input an "X" in one or more of the cells, the formula returns "Not
Complete", which is correct.

however, if, for example, i input an "X" in cell A1 and an "A" in B1,
the value that needs to be returned is "Not Complete", but instead i
get "OK". this is incorrect.

i have to use the letter "X".

is there another function i should be using rather than lookup that
looks for a specific value and ignores all others?

any help will be greatly appreciated.

thanks for your time

karen

 

[Frank Kabel]

Hi
try using HLOOKUP instead
=IF(ISNA(HLOOKUP("X",A11,1,0)),"OK",(IF(HLOOKUP("X",A11,1,0)<>"X","
OK","Not Complete")))
you can change this to
=IF(ISNA(HLOOKUP("X",A11,1,0)),"OK","Not Complete")

or in respect to your requirement you may use:
=IF(COUNTIF(A11,"X")>0,"Not complete","OK")

Frank

 

[snsyg]

hi frank,

thanks so much for your help, the hlookup took care of the problem.

this is the formula that ended up working:
=IF(ISNA(HLOOKUP("x",F8:M8,1,0)),"Done",(IF(HLOOKUP("x",F8:M8,1,0)<>"x","Done","No
Complete")))

another formula you suggested,
=IF(ISNA(HLOOKUP("X",A11,1,0)),"OK","Not Complete") wasn't the righ
logic for what i was trying to accopmlish, but you had no way o
knowing that based on my post!

the countif formula you suggested worked initially, but didn't updat
the return value if the "x" was removed from the range of cells. th
formula needs to respond to dynamic input. perhaps the fomula neede
to be expanded upon, but it's not an issue anymore.

i'm not sure if you will check this thread again or not, but if you do
can you tell me exactly what the 1 and 0 represent in the formul
HLOOKUP("x",F8:M8,1,0)?

also, where do you learn all this?! i've seen your name on a lot o
replies.

i've exhausted all my resources for learning (except thi
forum)...local classes, excel and vba manuals, coworkers...do you hav
any other suggestions? are there any books you could recommend
especially with regard to cell fomulas and also vbe?

thanks so much for your help! it is much appreciated

kare

 

[Frank Kabel]

Hi
first thanks for the feedback. Asnwers to your question see below

Frank

the countif formula you suggested worked initially, but didn't update
the return value if the "x" was removed from the range of cells. the
formula needs to respond to dynamic input. perhaps the fomula needed
to be expanded upon, but it's not an issue anymore.

COUNTIF should work. If you change data in your range COUNTIF should
reflect this.

i'm not sure if you will check this thread again or not, but if you
do, can you tell me exactly what the 1 and 0 represent in the formula
HLOOKUP("x",F8:M8,1,0)?

The third parameter (1) reflects the row index from the 2nd parameter.
So in this case return the first row from F8:M8 -> that is row 8.
If you hat a formula like: =HLOOKUP("x",F8:M9,2,0)
Excel will search the 'x' in row 8 and will return the value from the
row below (the 2nd row of your search range)

The 4th parameter (0) tells Excel to look for an excat match of your
'x'. The other valid value for this 4th parameter is 1 which will work
for sorted ranges and will return the nearest value, if your search
criteria is not found (for more info, lookup the Excel helpfile)

i've exhausted all my resources for learning (except this
forum)...local classes, excel and vba manuals, coworkers...do you have
any other suggestions? are there any books you could recommend,
especially with regard to cell fomulas and also vbe?

Have a look at this list of Excel books:
http://www.contextures.com/xlbooks.html
I personally like John Walkenbach's books:
http://j-walk.com/ss/books/index.htm