Forum Discussion

WJPalermo's avatar
WJPalermo
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 following works but is pretty ugly (to me), and, I need to do it a few times.

=IFS(ISNUMBER(MATCH(D6,'OC Gen Serv Sites'!$A$7:$A$700,0)),"OC Gen Serv#1",ISNUMBER(MATCH(D6,'OC Gen Serv Sites'!$B$7:$B$700,0)),"OC Gen Serv#2",ISNUMBER(MATCH(D6,'OC Gen Serv Sites'!$C$7:$C$700,0)),"OC Gen Serv#3",ISNUMBER(MATCH(D6,'OC Gen Serv Sites'!$D$7:$D$700,0)),"OC Gen Serv#4",ISNUMBER(MATCH(D6,'OC Gen Serv Sites'!$E$7:$E$700,0)),"OC Gen Serv#5",ISNUMBER(MATCH(D6,'OC Gen Serv Sites'!$F$7:$F$700,0)),"OC Gen Serv#6",ISNUMBER(MATCH(D6,'OC Gen Serv Sites'!$G$7:$G$700,0)),"OC Gen Serv#7",ISNUMBER(MATCH(D6,'OC Gen Serv Sites'!$H$7:$H$700,0)),"OC Gen Serv#8",ISNUMBER(MATCH(D6,'OC Gen Serv Sites'!$I$7:$I$700,0)),"OC Gen Serv#9",ISNUMBER(MATCH(D6,'OC Gen Serv Sites'!$J$7:$J$700,0)),"OC Gen Serv#10",TRUE,"NO MATCH")

 

excel
Formulas and Functions

6 Replies

  • JMB17's avatar
    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's avatar
      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's avatar
        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")

Resources