Excell Function-Compare a Cell to a Column of Names to return a value

J

jbadman

I am trying to compare a cell with a name in it to a column with a lis
of names in it. If the name is found I want it to put in
result(text) into another cell. If the name is not in the column
want it to compare it to another column and again return a resul
(text) if the name is not in the second column, I want excell to put i
a defult result (text). I tried using an IF function, but I can no
get it to work
 
K

Ken Wright

One way, assuming value to be looked up is in A1 and ranges being looked in are
C1:C10 and E1:E10

=IF(NOT(ISNA(MATCH(A1,C1:C10,0))),"1st
Col",IF(NOT(ISNA(MATCH(A1,E1:E10,0))),"2nd Col","Not There"))
 
K

Ken Wright

Just another way

=CHOOSE((COUNTIF(C1:C10,A1)+COUNTIF(E1:E10,A1)*2+1),"Not There","1st Col","2nd
Col")

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :)
----------------------------------------------------------------------------



Ken Wright said:
One way, assuming value to be looked up is in A1 and ranges being looked in are
C1:C10 and E1:E10

=IF(NOT(ISNA(MATCH(A1,C1:C10,0))),"1st
Col",IF(NOT(ISNA(MATCH(A1,E1:E10,0))),"2nd Col","Not There"))
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel - Wich Function should I use? 1
Excel does not accept text format! 0
formula to compare a values and return 2
Comparing 2 columns from 2 different Worksheet and Returing Value of a 3rd column 2
Finding the proper style field and the numbered paragraph value within the style 0
Drop down list control value in vba 2
Finding the right style field in VBA 0
Need help modifying code 0

Top