Lookup with blank spaces question

QuestionEdit

  I am trying to do a lookup, using a string on one sheet (Sheet1), and looking for the same string on another sheet (Sheet2) in the same workbook. If I find a match, I return a value in a corresponding column. The problem is, the array of strings on Sheet2 has trailing blanks. I don't want to have to manually find/replace the blanks. I'd rather the formula do that.

  I am using the following formula and it breaks down as follows:

=INDEX(INDIRECT("'" & $C$1 & "'!" & $S$1),MATCH(A4,INDIRECT("'" & $C$1 & "'!" & INDIRECT($T$1)),0))

$C$1: Contains the name of sheet 2 (Sheet2) $S$1: Contains the column from which to return the lookup value ($E$2:$E$100) A4: Contains the string to lookup on Sheet2 $T$1: Contains the array of strings with the trailing blanks($A$2:$A$100)

Any help would be appreciated.

AnswerEdit

Lets start with the easiest fix

=INDEX(INDIRECT("'" & $C$1 & "'!" & $S$1),MATCH(A4&"*",INDIRECT("'" & $C$1 & "'!" & INDIRECT($T$1)),0))

This will only have problems if the value in A4 could be shorter substring of the values in the lookup column

for example. If i Lookup ABCD then match("ABCD*" will match a value of "ABCDEFG " if it appears earlier than "ABCD "

If that isn't a problem, then what I gave you is the easiest fix.

otherwise you would need to replace

INDIRECT("'" & $C$1 & "'!" & INDIRECT($T$1)) with

Substitute(INDIRECT("'" & $C$1 & "'!" & INDIRECT($T$1))," ","")

and array enter your whole formula (enter with Ctrl+shift+enter rather than just enter)

Also note that if you data is pulled in from the internet, it can often have Char(160) [non breaking space) in it which looks like a space but isn't. then you would need to do

substitute(Substitute(INDIRECT("'" & $C$1 & "'!" & INDIRECT($T$1))," ",""),char(160),"")