Joining to formula not given expected result

First of all you're multiplying the VLOOKUP by the last COUNTIFS ONLY

You need to put ALL of the COUNTIFs within brackets.

=(COUNTIFS(G6,">=8",G6,"<>*m*")+COUNTIFS(I6,">=8",I6,"<>*m*")+COUNTIFS(K6,">=8",K6,"<>*m*")+COUNTIFS(M6,">=8",M6,"<>*m*")+COUNTIFS(O6,">=8",O6,"<>*m*")+COUNTIFS(Q6,">=8",Q6,"<>*m*")+COUNTIFS(S6,">=8",S6,"<>*m*"))*VLOOKUP(C6,Names[[Name]:[Travel]],6,0)

What result are you getting, you haven't said, you're just implying it can't be done?

Dunno why I didnt spot this before.
Why are using COUNTIFS on an individual cell? The result can only be 1 or 0.
Is it because it allows you to enter wild cards?

Only way i can see is

=((G6>=8)*(ISNUMBER(SEARCH("m",G6)))+(I6>=8)*(ISNUMBER(SEARCH("m",I6)))+(K6>=8)*(ISNUMBER(SEARCH("m",K6)))+(M6>=8)*(ISNUMBER(SEARCH("m",M6)))+(O6>=8)*(ISNUMBER(SEARCH("m",O6)))+(Q6>=8)*(ISNUMBER(SEARCH("m",Q6)))+(S6>=8)*(ISNUMBER(SEARCH("m",S6))))*VLOOKUP(C6,Names[[Name]:[Travel]],6,0)

Wondering if a SUMPRODUCT will work...

I'm now unsure sure that above formula will work...

Try

=SUMPRODUCT((G6:S6>=8)*(MOD(COLUMN(G6:S6),2)=1)*(ISNUMBER(SEARCH("m",G6:S6))))*VLOOKUP(C6,Names[[Name]:[Travel]],6,0)

SUMPRODUCT seems to work on some test data, (entering 9 and 9m into G6:S6 produced correct results)

The MOD() is there so we only extract data from odd numbered columns in G6:S6, ie columns G I K M O Q S