# Lookup with blank spaces question

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:44 PM GMT

## Question

```  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.

=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),"")