# How to find specific RANGE ADDRESS from array based on value from the first column?

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`?

-

## migrated from stackoverflow.comJan 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.

-
Battleships is a nicest sample ever for `MATCH`, my upvote for that) – Peter L. Jan 24 '13 at 13:07
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)))`

-