Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 0 down vote favorite

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 at 14:16

2 Answers

up vote 1 down vote

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 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 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 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 at 1:06
up vote 0 down vote

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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