How to return multiple results in a search

[jrlafrance]

I've got a small problem that I can't seem to figure out and need some help. I've attached the file for example. On the "PT Scores" tab, there is a status "MEB" listed next to a couple of names. I need to be able to return then names to the "Status" tab, under MEB:, preferably in a comma separated format. I'm using a government system and cannot use VB.

[leelnich]

Hi jrlafrance- I have a solution that (currently) involves adding a helper column on the PT Scores sheet. This holds names in this format: "Smith, John".
Paste this ARRAY FORMULA in Status!A2, then press CTRL+SHIFT+ENTER to confirm. Now use the drag handle to fill down.

=IFERROR(INDEX(('PT Scores'!$D$2:$D$10000),MATCH(0,(COUNTIF(Status!$A$1:$A1,'PT Scores'!$D$2:$D$10000)+('PT Scores'!$C$2:$C$10000<>Status!$A$1)),0)),"")

NOTE: If after pasting, the formula appears as TEXT, a leading space got copied. Return to the formula bar and delete the space, then
press CTRL+SHIFT+ENTER again.

Please see the attached workbook. I will attempt to eliminate the helper column. Check back in 45 min. -Lee

Helper Column removed. See post # 5 for updated versions of workbook and formula.

[leelnich]

I have some questions:
Are the names on the PT Scores list unique, or can the same person appear more than once?
If a person can appear twice, can that person also have 2 MEB's?
Roughly how many names on the list?

My original formula assumed names on the PT Scores list were NOT unique. It can be simplified if that is not the case.

[jrlafrance]

All the names are unique, and there are roughly 150 of them. Only 1 person can have 1 MEB.
I tried to download the file you attached, but was unable. I think the problem might stem from being on a government system.

[leelnich]

OK, I was able to eliminate the extra column. Names in the MEB column are in this format: "Smith, John", all one word.
IMPORTANT: In cell A1 of the Status Sheet, the header "MEB:" must be changed to "MEB" to conform with the spelling on the other Sheet.

Paste this ARRAY FORMULA in Status!A2, then press CTRL+SHIFT+ENTER to confirm. Now use the drag handle to fill down.

=IFERROR(INDEX('PT Scores'!$A$1:$A$1000 & ", "&'PT Scores'!$B$1:$B$1000,SMALL(('PT Scores'!$C$1:$C$1000<>Status!$A$1)*50000+ROW($A$1:$A$1000),ROWS($A$1:$A1))),"")

NOTE: If after pasting, the formula appears as TEXT, a leading space has been copied and pasted. This is a known issue with the website.
Return to the formula bar and delete the space to the LEFT of the "=" sign, then press CTRL+SHIFT+ENTER again.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[jrlafrance]

So as soon as I click anywhere in the worksheet, in cell A2 it changes from "Smith, John" to "LastName, First Name". And when I drag the formula down, the following cells display nothing. This is pretty advanced for me, I'm not understanding why you added "*5000" into the formula. The first formula worked well, I might just stick with that one.

[leelnich]

No problem, you just didn't get the ARRAY FORMULA confirmed correctly. Select A2 and then press the F2 key. This puts you in the formula bar. Now press CTRL+SHIFT+ENTER all at the same time. Curly brackets {} should appear around the formula, indicating the ARRAY status is confirmed. Now look for the little green box at the bottom right corner of the cell. That's the "drag handle". Click and hold while you DRAG down the column to extend the selection. This fills (copies) the formula to the cells you select. Go down as far as you need to. NOW you should see correct results. Remember, CTRL+SHIFT+ENTER must be pressed WHENEVER you finish editing an ARRAY FORMULA.

[leelnich]

=IFERROR(INDEX(AllLastNames" , "AllFirstNames,SMALL((AllReasons<>MEB)*50000+AllRowNumbers, RowToShow)),"")

Let me try to explain. As I've been saying, this is an Array Formula. That means it works with a bunch of inputs, but produces just one result. Lots of built-in Excel functions do this, like SUM, COUNT, MAX, and one I've used here: SMALL(V1,V2,V3,...,k_Number). This function looks at an array of values, and returns the k-th smallest. So, in this case, I build an array by looking at each row to see if MEB is present. If TRUE, 0. If FALSE,1. Then I multiply each 1 or 0 by 50000 and add the number of the row where it occurs. This gives me a list containing the row number of every row where MEB was found, and the row number + 50000 of every row where it was NOT found. Now SMALL comes into play. The same list is generated in every cell containing the formula, but each of those is on a DIFFERENT ROW. So each cell uses its own Row Number to determine which value in the list to display. First (top) cell displays the 1st smallest value, Second cell displays the 2nd smallest, and so on, until there are no more values to show.

[jrlafrance]

In the formula, what worksheet are these referring to "ROW($A$1:$A$1000),ROWS($A$1:$A1)"? I've tried inserting this into my workbook, but I haven't gotten it to work yet.

[AlKey]

Enter array formula in A2 on Status Sheet and copy down

Please Login or Register  to view this content.

**Must be entered with Ctrl+Shift+Enter key combination.

v	A
1	MEB
2	Smith, John
3	Peters, Derrick
4
5
6
7
8