Using Index/Match to pull multiple columns?

[somethingwicked]

Hi all,

I have a value I'm looking up with an index/match formula, and the thing is this value also corresponds to data in another column that I'm also trying to pull. I want the data in those two columns to be reflected in my index/match statement

Right now, I've got two very similar index/match formulas like this:

=INDEX('Zone 1'!$H:$H,MATCH(Master!E4,'Zone 1'!$G:$G,0))

=INDEX('Zone 1'!$I:$I,MATCH(Master!E4,'Zone 1'!$G:$G,0))

Is there any way to consolidate these two formulas into one? I want the data in those two columns to be reflected in one index/match statement so they can output both of the values returned from the columns in one cell.

Thanks for your help. I'm trying to improve my index/match knowledge and this is throwing me for a loop!

 

[godsaaint]

Is there any way to consolidate these two formulas into one? I want the data in those two columns to be reflected in one index/match statement so they can output both of the values returned from the columns in one cell.

Well, it's not one index/match but if you just need to get the job done, just concatenate them:

=INDEX('Zone 1'!$H:$H,MATCH(Master!E4,'Zone 1'!$G:$G,0))&", "&INDEX('Zone 1'!$I:$I,MATCH(Master!E4,'Zone 1'!$G:$G,0))

 

[somethingwicked]

This works great. Thanks!

One last question, so, this is basically two index match statements added together -- would an index match match statement also work?

 

[godsaaint]

This works great. Thanks!

One last question, so, this is basically two index match statements added together -- would an index match match statement also work?

As far as I know, no, you could pull both results into an array using only one index/match by putting a 0 in the argument Column_Num, for example, =INDEX('Zone 1'!$H:$I,MATCH(Master!E4,'Zone 1'!$G:$G,0),0) would technically return an array {1st result,2nd result}, however at the end the formula will only pull the 1st result. This is useful though if you are dealing with numeric values and you want to sum them or do something else.

If you want to bring multiple results concatenated into one single cell using only one formula, You would have to use VBA, see this thread for reference: http://www.mrexcel.com/forum/excel-...urn-multiple-values-one-cell-concatenate.html