The value in vlookup array has spaces but the lookup value does not

Hi All,

I have a formula which uses VLOOKUP function. The lookup array I use for this formula has some text values in it which starts with some space. An example would be " Hello" instead of "Hello". However, my lookup value is just "Hello" and hence the vlookup formula does not work. I do not want to just type in the text for the lookup value as I want to copy the formula down. Trim and substitute functions come to mind but I could not manage to get them to work. The formula is pasted below. The formula might seem a bit daunting but in essence it is just a vlookup formula.

Any ideas? Thanks in advance

Formula:

Please Login or Register  to view this content.

Applying trim to the lookup array did work. However, it simply slowed the process way down, which, I'm guessing is what Glenn was talking about. The formula with the LOOKUP function did not work either though. It just returns the "too many arguments" error. The reason why I used MATCH and ABS is to count the number of columns after copying the formula to the right of the row by the way.