It's been a long time since my last work on Excel formulae and I'm having problems with =INDEX and =MATCH. I have this array:

A     B    C    D    E

and from there, I would like to get the array from column B to column E based on values from column A. So for example, if I have the value "arg", using an Excel formula how can I return the range B3:E3?


Belatedly, given your comments, I realised that you actually want the range as a text string - you could do that like this:


| improve this answer | |


=INDEX(YourRange, MATCH(A1, FirstColumn, 0), 2)

Where YourRange is the range of your table in your example and FirstColumn is column A in your table. A1 is the cell with the item you want to look up. The zero in the MATCH function is there to find the exact match and the 2 is the column you want to pull back. So 2 for B, 3 for C etc..

I think of INDEX as a game of Battleships the first parameter is your playing board, the second parameter is how many rows down and the last parameter is how many columns across.

| improve this answer | |
  • Battleships is a nicest sample ever for MATCH, my upvote for that) – Peter L. Jan 24 '13 at 13:07
  • 1
    I think that the required result is the whole row (B3:E3) given as example, so for that you can use zero in place of 2 in the formula, e.g. =INDEX(B1:E4,MATCH("arg",A1:A4,0),0). Of course the result only makes sense if used in a function that expects a range of values – barry houdini Jan 24 '13 at 14:39
  • I have used the formula of Dave and barry but still i'm not getting the text "B3:E3".. it just returned "0".. i did tried also entering that formula with ctrl+shift+enter, thinking that it could work, but no.. i hope there's a good answer for this.. – E-r Gabriel Doronila Jan 25 '13 at 0:59
  • i think there should be something with the address and offset function.. but im not sure how.. any ideas? i need to get the range "B3:E3" or "$B$3:$E$3" – E-r Gabriel Doronila Jan 25 '13 at 1:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.