Forum Discussion

Copper Contributor

Dec 20, 2020

Easier method for IFS(ISNUMBER(MATCH) multiple column search?

There must be an easier way to do this but I cannot figure it out. I need to search 10 columns for a specific value, when found return specific text( text to be used in another formula). The followin...

excel

Formulas and Functions

JMB17

Bronze Contributor

Dec 21, 2020

One way I believe you can do it (hit Ctrl+Shift+Enter after keying/copying the formula into the formula bar):

="OC Gen Serv#"&MIN(IF('OC Gen Serv Sites'!$A$7:$J$700=D6,TRANSPOSE(ROW(INDIRECT("1:"&COLUMNS('OC Gen Serv Sites'!$A$7:$J$700)))),""))

WJPalermo
Copper Contributor
Dec 21, 2020
JMB17 Works perfectly! Appreciate it.
FYI, mucked it up a little by adding an IF to return "NO MATCH" when a value not found. (was returning "OC Gen Serv#0")
- mtarler
  Silver Contributor
  Dec 21, 2020
  WJPalermo Alternately (without using INDIRECT):
  =IFERROR("OC Gen Serv#"&AGGREGATE(15,6, COLUMN('OC Gen Serv Sites'!$A$7:$J$700)/('OC Gen Serv Sites'!$A$7:$J$700=D6),1),"NO MATCH")
  - JMB17
    Bronze Contributor
    Dec 21, 2020
    Although that will return an incorrect result if the table is moved (or a column inserted to the left) and the left most column is no longer in Column A.
- JMB17
  Bronze Contributor
  Dec 21, 2020
  You're welcome.