Forum Discussion
Solved
Formula Help
Hello!
I am looking to add a formula into an excel document in which it looks at three different columns of data and based on which column it is in, leaves a specific value. Is this possible?
For instance, in cell A1, I want to know if the value in cell B1 is in column 3, column 4, or column 5. If in column 3, type "1"; if in column 4, type "2", if in column 5, type "3"; if not in any column, leave blank.
Thank you for your help in advance!
pemorisak As a variant, perhaps the attached file contains something you could use. It finds the number from B1 in the data array (C1:E15 in this example). This returns a single number 1 (if found) and many zeros or all zeros if not found. Then, MMULT enables you to "calculate the column number in which B1 is found (1, 2 or 3). If not found it returns 0 which you then suppress (make invisible) with a custom format.
A1 contains this formula:
=SUM(MMULT(IFERROR(FIND(B1,C1:E15),0),{1;2;3}))
pemorisak As a variant, perhaps the attached file contains something you could use. It finds the number from B1 in the data array (C1:E15 in this example). This returns a single number 1 (if found) and many zeros or all zeros if not found. Then, MMULT enables you to "calculate the column number in which B1 is found (1, 2 or 3). If not found it returns 0 which you then suppress (make invisible) with a custom format.
A1 contains this formula:
=SUM(MMULT(IFERROR(FIND(B1,C1:E15),0),{1;2;3}))
