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

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

  1. Acquistare Salbutamol

    Hello would you mind letting me know which hosting company you’re working with? I’ve loaded your blog in 3 completely different browsers and I must say this blog loads a lot quicker then most. Can you suggest a good web hosting provider at a fair price? Thanks a lot, I appreciate it!

  2. anabole steroide kaufen

    Youre so cool! I dont suppose Ive learn something like this before. So nice to search out someone with some original thoughts on this subject. realy thanks for starting this up. this web site is something that’s wanted on the web, somebody with slightly originality. useful job for bringing one thing new to the web!

  3. gunstige im internet kaufen

    When I initially commented I clicked the -Notify me when new comments are added- checkbox and now each time a remark is added I get four emails with the same comment. Is there any method you can take away me from that service? Thanks!

  4. In Deutschland

    You really make it seem really easy along with your presentation however I in finding this topic to be actually something which I believe I would never understand. It kind of feels too complex and extremely broad for me. I am taking a look ahead for your next put up, I will attempt to get the hold of it!

  5. ig

    Hi there, all is going perfectly here and ofcourse every
    one is sharing facts, that’s in fact fine, keep up writing.


    I’m extremely impressed together with your writing abilities as
    neatly as with the structure in your blog.
    Is that this a paid subject matter or did you modify it your
    self? Anyway keep up the excellent high quality writing, it’s
    rare to see a nice weblog like this one these days..

  7. this article

    I keep listening to the news speak about receiving boundless online grant applications so I have been looking around for the most excellent site to get one. Could you advise me please, where could i acquire some?

  8. Josiah Deserio

    Heya i am for the first time here. I found this board and I find It really useful & it helped me out a lot. I hope to give something back and help others like you helped me.

Comments are closed.