Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
app
csd
arg
egd

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?

share|improve this question

migrated from stackoverflow.com Jan 24 '13 at 14:16

This question came from our site for professional and enthusiast programmers.

INDEX AND MATCH example:

=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.

share|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

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

=CELL("address",INDEX(B1:B4,MATCH("arg",A1:A4,0)))&":"&CELL("address",INDEX(E1:E4,MATCH("arg",A1:A4,0)))

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .