Vookups and index match not working on text in a cell derived from concatenation formula

[richardcarter]

I am trying to do a VLOOKUP (or an INDEX/MATCH) on cell A1 on Sheet 1 which contains the words 'Green Apples'. However the words 'Green Apples' in this cell have been derived from text concatenation so although the cell shows the words 'Green Apples', the cell actually contains the formula:

=Sheet2!$A$1&" "&"Apples" (so the word 'Green' is coming from cell A1 on Sheet 2).

What I am trying to do now is another vlookup based on Cell A1 Sheet 1 but the following formula throws up #N/A (and the same result if I use index/match instead). However, if I take out the concatenated formula and just type in the words 'Green Apples' into cell A1, then the formula works fine.

=VLOOKUP("Green Apples",Sheet1!A1:C20,3,FALSE)

Suggestions appreciated!

 

[Robert Mika]

Check for any spaces befor or after the "Green" word or try if this helps:
=TRIM(Sheet2!$A$1)&" "&"Apples"

 

[richardcarter]

Solved - Thank you! I did have spaces in there.... but I put them in their deliberately as I need them for other referencing... but I think I can tweak it using your TRIM suggestion and cutting off characters using LEFT(x,x) RIGHT(x,x) trim functions.