Extracting a factor from an Excel table by searching an input range
Solution 1:
You could try:
Formula in M2
:
=MAX(FILTER(B2:J2,--SUBSTITUTE(LEFT(B1:J1,FIND("-",B1:J1&"-")-1),">","")<=M1))
Just for the sake of it, a little personal (unnecessary) exercise, this could be done with FILTERXML()
:
=FILTERXML("<x>"&TEXTJOIN("</s></t>",,"<t>"&B2:J2&"<s>"&B1:J1&"-")&"</s></t></x>","//t[substring-before(translate(.//*,'>',''), '-')<="&M1&"][last()]")