Excel use index match to return multiple values in a list

Question

My worksheets:

ELKK   BSN   Voornaam   Achternaam   DOB  Basisschool   Advies   Voorrangschool   Voorkeur
  1     1       John       Smit       1        Test      VWO          Test           Test
  2     2       Chris      Kong       2        Test 2    HAVO         Test           Test

This worksheet is called Leerlingen

The second one:

School   Advies   Klasnaam   Regulier
 Test     VWO       VWO         1
 Test 2   HAVO      HAVO        1
 Test 3   VWO       SPORT       0

This worksheet is called VO-scholen

What I want to happen is, when I change the value "Advies" in the Leerlingen worksheet. I want to get all the schools from the VO-scholen Worksheet who also have that "Advies" and return them in the Leerlingen worksheet into a list under the column "Voorrangschool".

This means that with "John Smit" I can select the schools Test and Test 3, and with "Chris Kong" I can select the school Test 2.

Here is what I already tried:

=INDEX('VO-scholen'!A2:G4;MATCH(G2; 'VO-scholen'!$C$2:$C$4; 0);2 )

But it won't return multiple values, only the first value it can find.

Unfortunately, you cannot concatenate a range or an array in Excel without using VBA. One such solution is at http://stackoverflow.com/questions/13975471/array-formula-result-concatenated-into-single-cell/13994045#13994045. I myself would use the IF method for this instead of INDEX and MATCH. — Rick Hitchcock, Oct 29 '14 at 14:01
@RickHitchcock Thanks for your reply, I'll look into the VBA solution and see if I can get it to work. Thanks — Jan-Willem de Boer, Oct 29 '14 at 14:10

score 0 · Answer 1 · answered Oct 29 '14 at 15:49

0

my answer requires MOREFUNC addon*

Here I assume data in purchase "table" is in A9:C11. Adjust accordingly.

formula for Leerlingen!H2: =MCONCAT(IF('VO-scholen'!$C$2:$C$4=G2,'VO-scholen'!$B$2:$B$4,""),",")

notice the curly braces. This is an array formula you have to confirm using Ctrl+Shift+Enter, not just Enter (so that curly braces appear)

then copy the formula down

MOREFUNC ADDON

Morefunc Addon is a free library of 66 new worksheet functions.
HERE is some information (by original author)
here is the last working download link I found
here is a good installation walk-through video

answered Oct 29 '14 at 15:49

user3616725

3,485
1
18
27

I cant get the MOREFUNC addon for OSX :( – Jan-Willem de Boer Oct 29 '14 at 16:08
you should be able to add a user defined function MCONCAT by hand, depending on the version of Office you have installed. I understand you need a Office 2011 or newer, with VBA option installed. – user3616725 Oct 30 '14 at 09:31

score 0 · Answer 2 · answered Oct 29 '14 at 18:42

You'll want to modify this a little, but try:

=INDEX('VO-scholen'!$A$1:$G$4,MATCH($G2, 'VO-scholen'!$B$1:$B$4, 0),1 ) & IF(COUNTIF('VO-scholen'!$B:$B,Leerlingen!$G2)>1, ", " & INDEX(OFFSET('VO-scholen'!$A$1:$D$4,MATCH($G2,'VO-scholen'!$B$1:$B$4),0),MATCH($G2,'VO-scholen'!$B$1:$B$4),1),"")

The first index match grabs the first value in the table. The second index match grabs the first value in an offset from the first table. The offset starts the second lookup table down the number of rows from the first lookup value. In other words if you were searching in B1:B100 and the value was found in row 3, the second lookup will be in B4:B104.

Your example didn't seem to be returning what you were describing so I guessed that you were looking up the value in the Advies column on "Leerlingen" from the Advies column on sheet "VO-scholen" and returning the value in the "School" column. Oh, and you'll want to replace my commas with semi-colons in the formula above. — gwhenning, Oct 29 '14 at 18:48

Excel use index match to return multiple values in a list

2 Answers2