Using the text of a cell range (array?) in VLOOKUP or MATCH formula


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

Leave a Reply

Your email address will not be published. Required fields are marked *