SOLVED

# Writing an IF function with multiple values in one cell

Occasional Contributor

# Writing an IF function with multiple values in one cell

Hello,

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)
Solution

# Re: Writing an IF function with multiple values in one cell

In B2:

=IF(\$A2="","",--ISNUMBER(FIND(","&COLUMN()-1&",",","&\$A2&",")))

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

See the attached version.

# Re: Writing an IF function with multiple values in one cell

No IF().

See attached file.

# Re: Writing an IF function with multiple values in one cell

As variant

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

# Re: Writing an IF function with multiple values in one cell

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?

# Re: Writing an IF function with multiple values in one cell

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?

# Re: Writing an IF function with multiple values in one cell

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?

# Re: Writing an IF function with multiple values in one cell

One other thing- would you be able to walk me through the syntax and what each component does? Thanks so much!

# Re: Writing an IF function with multiple values in one cell

Why are there blank cells? You don't need a formula for blank cells.

# Re: Writing an IF function with multiple values in one cell

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

For example

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

# Re: Writing an IF function with multiple values in one cell

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.