dynamic excel array based on input -

i looking little in making formula based dynamic array in excel.

kpi | tgt | number | weight

fcr | 0% | 1 | 45%

fcr | 60% | 2 | 45%

fcr | 80% | 3 | 45%

leads | 45% | 4 | 25%

leads | 50% | 5 | 25%

leads | 200% | 6 | 25%

attrition | 8% | 7 | 10%

attrition | 12% | 8 | 10%

attrition | 100% | 9 | 10%

abandon | 1% | 10 | 20%

abandon | 5% | 11 | 20%

abandon | 200% | 12 | 20%

so if have leads score in cell e2 3%, want output in f2 number 4 <45% hence 4. ps: have spreadsheet don't know how attach it.

try in f2:

=index(c:c,aggregate(15,6,row(b:b)/((b:b>=e2)*(a:a="leads")),1)) 

this return matches rows "leads" in column a. can made reference if have or want put cell instead.

edit:

based on you're comment below, formula works if entered array (ctrl + shift + enter):

=index(c:c,match(1,(a:a="leads")*(b:b>=e2),0)) 

edit 2:

we can cover our bases unsorted list in column b combining 2 solution:

{=index(c:c,match(1,(a:a="leads")*(b:b=aggregate(15,6,b:b/((b:b>=e2)*(a:a="leads")),1)),0))}