Returning multiple values with INDEX function

[Goose306]

Hey all,

I've been trying to get a formula working based on several examples I've seen elsewhere but I'm getting no dice. Here's the situation:

I have a table I'm trying to fill in with data that is coming from another tab. The original data is a list with two columns - one has ID numbers (mixed alphanumeric) the other is a list of accounts. The table I'm filling has the ID numbers vertically, and then has the accounts filled in horizontally to the right of the ID numbers. My table I'm filling is located from B2:M31, the data I'm pulling is located from O:P (IDs in O, accounts in P) There is also a row of numbers from A2:A31 that is not correlated to the data I'm trying to compare (it's to keep track of sorting, although if I can get this formula working I won't need it!)

To fill this previously, I've just been copy/pasting with a transpose paste. I'd like to get it more efficient, however. Due to this, I've been messing with some INDEX functions. Now, I will admit I haven't messed with these much in the past, so I'm a bit of a newbie in this area. However, I seem to have gotten it working for some ID numbers, but not others, and its driving me nuts. Here is the two formulas I've tried:

=INDEX($P$2:$P$2000, SMALL(IF($B3=$O$2:$O$2000, ROW($O$2:$O$2000)-ROW($O$2)+1), COLUMN($A1)))

This one works for the first ID, however I have to manually change the '+1' in the formula to a +2, +3, etc. as it goes horizontally. This wouldn't be that big of an issue, however once I move the formula down a row to the next ID (referencing cell $B4) it starts pulling wrong data (still pulling accounts from the ID located at $B3 - even though it's updated)

The other formula I tried was:

=INDEX($P$2:$P$2000, SMALL(IF($B4=$O$2:$O$2000, ROW($O$2:$O$2000)-MIN(ROW($O$2:$O$2000))+1, ""), COLUMN($A1)))

This works for the first ID (again, manually changing the +1 as it goes horizontally), however throws VALUE errors as soon as I try to drag it down to the next ID.

Any help would be appreciated. I unfortunately cannot upload any files for comparison as I'm on work internet, I hope my explanation was clear enough.

 

[gsistek]

The reason you get the #VALUE error is the formula is written as an array formula. To have it work properly, go into the formula bar and press CTRL-SHIFT-ENTER instead of just ENTER. You will get brackets {} around the entire formula if done correctly.

Also, Instead of using +1, +2, etc, try using +COLUMNS($A$1:A1). As the formula moves horizontally, this will change to +COLUMNS($A$1:B1) (which is 2), etc.

 

[Eli Ltd]

Hi there,

Thanks for trying to be as clear as possible. Unfortunately it's very hard for me to follow on my cell phone. However I've made so many spreadsheets like this in the past I think I understand the issue. If not, I'm sure plenty of brighter people than me can help you out. By using "(COLUMN(O$2)-14) you are just saying "+1" but now when you drag it it will add incrementally. It's just counting the column number and subtracting the 14 irrelevant columns because you start at Column O.

=INDEX($P$2:$P$2000, SMALL(IF($B3=$O$2:$O$2000, ROW($O$2:$O$2000)-ROW($O$2)+(COLUMN(O$2)-14)), COLUMN($A1)))

Let me know if I'm way off the mark as I'd be happy to take a proper look when I get home in about an hour.

Thanks,

Eli

 

[Goose306]

The reason you get the #VALUE error is the formula is written as an array formula. To have it work properly, go into the formula bar and press CTRL-SHIFT-ENTER instead of just ENTER. You will get brackets {} around the entire formula if done correctly.

Also, Instead of using +1, +2, etc, try using +COLUMNS($A$1:A1). As the formula moves horizontally, this will change to +COLUMNS($A$1:B1) (which is 2), etc.

Awesome! That seemed to do the trick. Interestingly, I read about the need to use CTRL-SHIFT-ENTER when I found these formulas, but the ones on the first ID seemed to be working fine without the array, so I figured it wasn't actually needed. Adding them did the trick to fix it with the rest of the IDs though.

Also thanks for the top with the columns. Again, the source of the original information didn't actually include that even though it said it could be dragged horizontally. Ah, if all internet sources could be as reliable as this forum!

 

[Goose306]

Hi there,

Thanks for trying to be as clear as possible. Unfortunately it's very hard for me to follow on my cell phone. However I've made so many spreadsheets like this in the past I think I understand the issue. If not, I'm sure plenty of brighter people than me can help you out. By using "(COLUMN(O$2)-14) you are just saying "+1" but now when you drag it it will add incrementally. It's just counting the column number and subtracting the 14 irrelevant columns because you start at Column O.

=INDEX($P$2:$P$2000, SMALL(IF($B3=$O$2:$O$2000, ROW($O$2:$O$2000)-ROW($O$2)+(COLUMN(O$2)-14)), COLUMN($A1)))

Let me know if I'm way off the mark as I'd be happy to take a proper look when I get home in about an hour.

Thanks,

Eli

Hi Eli,

Just a follow-up, gsistek's tip about making sure it's entered as an array did just the ticket, thanks for the follow-up!

 

[GerryZ]

Hello every body
I like this trick but I don't understand all of it probably because my first language is not English
It was nice to see a spreadsheet sample instead to use 1000 of words
Can someone of you post a screenshot or dropbox file so I can better understand?
Thank you in advance!

 

[Eli Ltd]

https://www.youtube.com/watch?v=v4Sx2LyJNcU