I have a defined list titled sawlist and in that list are the letters i.e. W and in the next cell down L and so on. I wrote a formula that is     su=sum(if(sawlist=B9,B10))  
I get a #value answer. My list is not numbers but letters and I am trying to write a letter in one cell and have the formula identify it as true if it matches a letter in another cell. How can I write this formula?

Thnaks Bill  
Who is Participating?
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:


=IF(ISNA(MATCH(B2,sawlist,0)),"no match", "match")

replace "no match" and "match" with what you want to happen. I did not quite understand the "sum" in that formula.

cheers, teylyn
Michael FowlerSolutions ConsultantCommented:

Not entirely sure what you are after could you provide an file.

Note to compare two cells regardless if they are letters you can use something like


BillkronmillerAuthor Commented:
The formula is a rather long and exhaustive formula and I am trying to make a defined list to replace all the "or" in this long boat. :) Here is the whole formula I am using and I have many others in this spread sheet. The entire workbook is about 15 megs and all formulas.


My list contaings all the letters i.e. "W" & "col" I use on "" marks just the letters and I would like to take the short cut that would allow me to to have say $EU20 = any of the letters in the saw list and without creating a much more difficult formula. In this formula I would have a number of different defined lists. Is this polssible?

Thanks Bill
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

I'm trying to make sense of your formula, but I'm not getting far. Can you post a spreadsheet with a simplified example that shows where your "sawlist" is and how you would want to involve that in a formula?

Once we get the general principles nailed, it may be easier to tackle the beast.

cheers, teylyn
Rory ArchibaldCommented:
It would definitely be easier with a workbook, though I think this is equivalent to what you posted but a bit shorter:

BillkronmillerAuthor Commented:
I don't believe that there is a complete answer to my question. I am trying to use a list name and have it be all encomapssing of all that is in the list. I don't think I can use the list as if it were one cell i.e.    If(or(a1,a2,a3    or    if(codelist   where codelist is all the cells a1,a2,a3
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.