Index Match data to provide multiple results in columns - Page 7
Thanks:  0
Likes:  0

# Thread: Index Match data to provide multiple results in columns

1. ## Re: Index Match data to provide multiple results in columns

Where is the link to the formula - I don't see it.
thanks

Originally Posted by lomond44
Using the example I linked to in my post, the data and results should look like this:

 Part No Assembly Lookup Match 1 Match 2 Duck Daffy Duck Daffy Mouse Mickey Mouse Mickey Minnie Mouse Minnie Bunny Bugs Bunny Bugs Pig Porky Pig Porky Dog Goofy Pluto Dog Goofy Dog Pluto

2. ## Re: Index Match data to provide multiple results in columns

Originally Posted by teeltea
Where is the link to the formula - I don't see it.
thanks
Post #4 shows the formula you can use...

3. ## Re: Index Match data to provide multiple results in columns

@ poer24

=IF(ISERROR(INDEX(\$A\$2:\$T\$2500,SMALL(IF(\$C\$2:\$C\$2500=\$AA\$1,ROW(\$C\$2:\$C\$2500)),ROW(4:4)),5)),"",INDEX(\$A\$2:\$T\$2500,SMALL( IF(\$C\$2:\$C\$2500=\$AA\$1,ROW(\$C\$2:\$C\$2500)),ROW(4:4)),5))

is not a formula that I would recommend or post... (There are enough people who start a site on Excel and propagate this kind of nonsense, alas.)

Try rather... In AB1 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(\$E\$2:\$E\$2500,SMALL(IF(\$C\$2:\$C\$2500=\$AA\$1,ROW(\$C\$2:\$C\$2500)-ROW(\$C\$2)+1),ROWS(\$AB\$1:\$AB1))),"")

4. ## Re: Index Match data to provide multiple results in columns

Hi,

I have a similar issue with these posts and I can't seem to figure it out. I have two tables on two separate sheets. Column A on both sheets is the one which matches the other. I want to find all the matches for Sheet 2:A from Sheet 1:A and put Sheet 1:B in Sheet 2:C-X.

For example;

 Sheet 1: A Sheet 1: B 13244 BS4W 8952 BCPLSAFCT5L 8952 WMB3FW 8952 EB3FX 8952 MFC10STHF

 Sheet 2: A Sheet 2: B Sheet 2: Match 1 Sheet 2: Match 2 Sheet 2: Match 3 Sheet 2: Match 4 13244 bbm1 bs4w 8952 hw10 bcplsafct5l wmb3fx eb3fx mfc10sthf 8952 hwa20 bcplsafct5l wmb3fx eb3fx mfc10sthf 8952 rlx4 bcplsafct5l wmb3fx eb3fx mfc10sthf

I attempted to use the formula you've used in reply #4 but I can't seem to get it to work (I did press Ctrl + Shift + Enter)

5. ## Re: Index Match data to provide multiple results in columns

@ Nugget_Leodis

Sheet1
(data)

 Row\Col A B 1 2 13244 BS4W 3 8952 BCPLSAFCT5L 4 8952 WMB3FW 5 8952 EB3FX 6 8952 MFC10STHF 7

Sheet2
(processing)

 Row\Col A B C D E 1 2 13244 BS4W 3 8952 BCPLSAFCT5L WMB3FW EB3FX MFC10STHF 4

In B2 control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX(Sheet1!\$B\$2:\$B\$6,SMALL(IF(Sheet1!\$A\$2:\$A\$6=\$A2,ROW(Sheet1!\$B\$2:\$B\$6)-ROW(Sheet1!\$B\$2)+1),COLUMNS(\$B\$2:B2))),"")

6. ## Re: Index Match data to provide multiple results in columns

Could you elaborate the below functions for me...? As I am not getting couple of ranges you referred to MATCH fuction, also before MATCH function threre is IF(\$B\$2:\$B\$14<>"" statement which I am not getting as well. thanks in advance.

=IFERROR(INDEX(\$B\$2:\$B\$14,SMALL(IF(FREQUENCY(IF(\$A\$2:\$A\$14=\$D2,
IF(\$B\$2:\$B\$14<>"",MATCH(\$B\$2:\$B\$14,\$B\$2:\$B\$14,0))),
ROW(\$B\$2:\$B\$14)-ROW(\$B\$2)+1),ROW(\$B\$2:\$B\$14)-ROW(\$B\$2)+1),
COLUMNS(\$E2:E2))),"")

7. ## Re: Index Match data to provide multiple results in columns

@ proficient

This refers to the formula which is forwarded in post #26 .

The bit:

FREQUENCY(IF(\$A\$2:\$A\$14=\$D2,
IF(\$B\$2:\$B\$14<>"",MATCH(\$B\$2:\$B\$14,\$B\$2:\$B\$14,0))),
ROW(\$B\$2:\$B\$14)-ROW(\$B\$2)+1)

of the formula creates maps the occurrences of the non-blank cells in B2:B14 to the bin-array ROW(B2:B14)-ROW(B2)+1, which correspond to the occurrences of D2 in A2:A14.

A. \$B\$2:\$B\$14<>"" means: skip blanks/empty cells.

B. MATCH(\$B\$2:\$B\$14,\$B\$2:\$B\$14,0) matches the range interest against itself, leading to numbers showing matches.

C. FREQUENCY works with numbers (data_array) whose occurrences it classifies into bins_array.

_____________________________
Intermezzo:

 item match item set against itself (data_array) bins_array count of B results of count B JAD 1 1 from 0 to >= 1 2 = 2 times 1 VAD 2 2 from 2 to >= 2 1 = 1 times 2 JAD 1 3 from 3 to >= 3 0 = 0 times 3 KAD 4 4 from 4 to >= 4 1 = 1 times 4 rest 0 = 0 times anything beyond 4 How many results are above 0? 3 This count is precisely the number of unique items JAD, VAD, and KAD.

_____________________________

D. The IF bit, i.e.

IF(FREQUENCY(IF(\$A\$2:\$A\$14=\$D2,
IF(\$B\$2:\$B\$14<>"",MATCH(\$B\$2:\$B\$14,\$B\$2:\$B\$14,0))),
ROW(\$B\$2:\$B\$14)-ROW(\$B\$2)+1),ROW(\$B\$2:\$B\$14)-ROW(\$B\$2)+1)

>>

IF(non-zero results of frequency, ROW(\$B\$2:\$B\$14)-ROW(\$B\$2)+1)

of the formula returns the rows of non-zero results.

E. Feeding the rows from [D] to INDEX looking at \$B\$2:\$B\$14 one by one by means of SMALL, we get the list of unique items from \$B\$2:\$B\$14.

See for more:
https://www.mrexcel.com/forum/excel-...ml#post3156949

Hope this helps.

8. ## Re: Index Match data to provide multiple results in columns

@ proficient

This refers to the formula which is forwarded in post #26 .

The bit:

FREQUENCY(IF(\$A\$2:\$A\$14=\$D2,
IF(\$B\$2:\$B\$14<>"",MATCH(\$B\$2:\$B\$14,\$B\$2:\$B\$14,0))),
ROW(\$B\$2:\$B\$14)-ROW(\$B\$2)+1)

of the formula creates maps the occurrences of the non-blank cells in B2:B14 to the bin-array ROW(B2:B14)-ROW(B2)+1, which correspond to the occurrences of D2 in A2:A14.

A. \$B\$2:\$B\$14<>"" means: skip blanks/empty cells.

B. MATCH(\$B\$2:\$B\$14,\$B\$2:\$B\$14,0) matches the range interest against itself, leading to numbers showing matches.

C. FREQUENCY works with numbers (data_array) whose occurrences it classifies into bins_array.

_____________________________
Intermezzo:

 item match item set against itself (data_array) bins_array count of B results of count B JAD 1 1 from 0 to >= 1 2 = 2 times 1 VAD 2 2 from 2 to >= 2 1 = 1 times 2 JAD 1 3 from 3 to >= 3 0 = 0 times 3 KAD 4 4 from 4 to >= 4 1 = 1 times 4 rest 0 = 0 times anything beyond 4 How many results are above 0? 3 This count is precisely the number of unique items JAD, VAD, and KAD.

_____________________________

D. The IF bit, i.e.

IF(FREQUENCY(IF(\$A\$2:\$A\$14=\$D2,
IF(\$B\$2:\$B\$14<>"",MATCH(\$B\$2:\$B\$14,\$B\$2:\$B\$14,0))),
ROW(\$B\$2:\$B\$14)-ROW(\$B\$2)+1),ROW(\$B\$2:\$B\$14)-ROW(\$B\$2)+1)

>>

IF(non-zero results of frequency, ROW(\$B\$2:\$B\$14)-ROW(\$B\$2)+1)

of the formula returns the rows of non-zero results.

E. Feeding the rows from [D] to INDEX looking at \$B\$2:\$B\$14 one by one by means of SMALL, we get the list of unique items from \$B\$2:\$B\$14.

See for more:
https://www.mrexcel.com/forum/excel-...ml#post3156949

Hope this helps.
The way you explain the formula step by step is awesome, thank you so much.

9. ## Re: Index Match data to provide multiple results in columns

Originally Posted by proficient
The way you explain the formula step by step is awesome, thank you so much.
Glad to hear that you like it.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•