Must Go Faster

So here is an option but I had to assume some stuff.

a) roots is all text (i.e. 100% is not a value of 1 but is "100%") and that 75-100% and average of 75 & 100 is good for the ranking and you want that max root value as 1st priority

b) counted is 2nd priority and you want larger number (i.e. count of 2290 and 100% root is preferred over count of 7000 but root of 75-100%)

c) Last Ver date you want 3rd priority and bigger is better?

Here is the formula i used:

=LET(s, SORTBY(HSTACK(ROW(Table1[item]),Table1[item]),BYROW(Table1[roots],LAMBDA(r,AVERAGE(--TEXTSPLIT(r,{"-","%"},,1)))),-1,Table1[counted],-1,Table1[last ver],-1),
XLOOKUP(B19,TAKE(s,,-1),TAKE(s,,1),"n/a",0,1))

basically it is a fancy SORT of the whole table based on the 3 criteria above then find the first occurence of the chosen item.

if the table is very large, it might be more efficient to filter first and then do the sort.

and this will just give you the row # in excel where to find it

note I added some dummy data to your set to show it works