Returning multiple values

[Threadsheet]

Hi there, was wondering if anyone would be able to give me a hand with a function I am using. Here it is below:

Please Login or Register  to view this content.

This nested function basically looks up a name on a different sheet and returns multiple values attached to that name, however it only works if the two columns are next to each other.

I would greatly appreciate if anyone could spread some light on how I could look up this name and return a value from a column that is far away from that, for example return values from A instead of D or E.

Thank you very much for any assistance.

[Richard Buttrey]

Normally the answer is to use a combination of INDEX(MATCH()) functions. However it's a little difficult to advise further without seeing the workbook.

However, and assuming your data is in a normalised two dimensional table then you'll probably find a Pivot Table will provide an answer and avoids needing a function altogether.

[Tony Valko]

=IF(ISERROR(INDEX(Activities_export!$D$1:$E$97,SMALL(IF(Activities_export!$D$1:$E$97=$G$2,ROW(Activities_export!$D$1:$E$97)),ROW(1:1)),2)),"",INDEX(Activities_export!$D$1:$E$97,SMALL(IF(Activities_export!$D$1:$E$97=$G$2,ROW(Activities_export!$D$1:$E$97)),ROW(1:1)),2))

Just index the column that you want to extract the data from.

Also, if you're using Excel 2007 or later you can reduce the formula length by half using the IFERROR function.

Here's a small example:

Data Range

	A	B	C	D	E
1	Region	Value	------	Region	Value
2	South	40		North	46
3	North	46			74
4	West	5			2
5	North	74			23
6	South	22
7	West	6
8	North	2
9	West	49
10	West	39
11	North	23
12	East	36
13	East	75
14	South	29
15	South	3

This array formula** entered in E2:

=IFERROR(INDEX(B:B,SMALL(IF(A$2:A$15=D$2,ROW(A$2:A$15)),ROWS(E$2:E2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

[Threadsheet]

Thanks for the replies guys.

Tony that function worked for what I wanted and made my first one a lot cleaner, thanks a lot!

[Tony Valko]

You're welcome. Thanks for the feedback!