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.
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),"")
Advertisement