Writing an IF function with multiple values in one cell

Occasional Contributor



This is my first post here. Please let me know if this is correct!


Device: PC/Windows 10 Pro 64-bit (Version 21H1 Build 19043)


Excel version: Microsoft® Excel® for Microsoft 365 MSO (Version 2109 Build 16.0.14430.20154) 32-bit


Context: I’m a researcher at a university and am working to write some formulas that will simplify data entry. I don’t have much experience in Excel. Currrently, I’m looking at student extracurricular involvement on campus. While some students are involved in one activity, many are involved in multiple. I wrote an IF formula to convert a single value into a binary system that we can use for other stats software and skip cells where students didn’t answer anything. However, if a student is involved in more than one activity, the formula doesn’t recognize multiple values and I’ll need to go into each column by hand and enter either a “1” if they are involved or a “0” if they aren’t. The sample file attached demonstrates this.


Expected result and method: Write a formula that can identify one or more values in a single cell and correctly “code” those values with either a 1 or a 0 in the subsequent columns.


10 Replies
best response confirmed by rcklein2 (Occasional Contributor)


In B2:


Fill to the right, then down (or vice versa) as far as needed.

See the attached version.


No IF().

See attached file.



As variant

  n, MID( A10, SEQUENCE(, LEN(A10)),1 ),
  j, --FILTER(n, n <> ","),
  IF( ISNA( XMATCH( SEQUENCE(,7),j )), 0, 1)
Hi Hans! This is fantastic! Thank you. I figured out how to use the formula in other columns- my actual data is in column "I" instead of "A". I just changed the number in COLUMN()-1 to COLUMN()-10. Is that a proper way?

Also, do you know a way to populate the cell with the actual value instead of the formula listed?
Hi Detlef! Thanks so much. Your formula currently fills in any blank cells with "0." I do need to keep these cells blank. Is there a way to do this using your formula?
Hi Sergei! Thanks so much. Your formula currently fills in any blank cells with "1." I do need to keep these cells blank. Is there a way to do this using your formula?
One other thing- would you be able to walk me through the syntax and what each component does? Thanks so much!
Why are there blank cells? You don't need a formula for blank cells.


Formulas in Excel do not return blank as a value, you may return empty string instead or any other symbols here


For example

  n, MID( A10, SEQUENCE(, LEN(A10)),1 ),
  j, --FILTER(n, n<>","),
  IF( ISNA( XMATCH( SEQUENCE(,7),j )), "", 1)



If the data are in column I instead of column A, I'd expect you would have to use COLUMN()-9 instead of COLUMN()-1 since column I is 8 columns to the right of column A, but the exact formula depends on the setup.

If you see a formula in the cell instead of its result:

  • Make sure that the cell with the formula is NOT formatted as Text. If it is, change the number format to General, then press F2 and Enter to re-enter the formula.
  • Make sure that 'Show Formulas' on the Formulas tab of the ribbon is off.