Indirect function and Array formula

VLOOKUP doesn't take a range or array as the first argument, try this version in S15 copied down

=VLOOKUP(substitute(D15,"-client",""),B$15:C$195,2,FALSE)

[I'm assuming D15 contains an entry like xyz_client, that will match with xyz in column B and return the corresponding value from column C]