Firstly sorry if my Excel lingo is a bit off as I’m self taught in Excel.
Okay so my problem is simple, say if I had the letters a,b,c,d,e in the corresponding cells of A2:E2.
I want to be able to put in cell A4, =”A2:E2″, which of course will return A2:E2 but as text.
But I then want to be able to refer to cell A4 in a MATCH formula, for example, =MATCH(“a”,A4,0), so to say I want it to look for “a” in whatever is in cell A4, which is the text of the cell range (array?) A2:E2 and find “a” and return 1.
Annoyingly (and a little obviously) the Match formula returns #N/A, but the question is that is there a way to force it to work? Maybe a function I can use in the “lookup_array” that will ‘untext’ whats in cell A4 for the match formula to be able to work?
Or a better way of storing the range A2:E2 in A4 for the match formula to be able to look in A4, find an array (A2:E2) and then use that array to look for “a”?
Any help would be appreciated.
By: Bradley Coomber