Forum Discussion
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...
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")
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")
- 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 True but the original formula wasn't exactly dynamic. but you can easily fix that by subtracting Column('OC Gen Serv Sites'!$A$7)-1
in fact why didn't you use that?
="OC Gen Serv#"&MIN(IF('OC Gen Serv Sites'!$A$7:$J$700=D6,COLUMN('OC Gen Serv Sites'!$A$7:$J$700)-COLUMN('OC Gen Serv Sites'!$A$7)+1,""))
