1

I have an alphabetized table containing a list of 85 employees. In the row for each individual employee, is a rank ordered list of the names of five organizations the individual employee would like to volunteer at. There are a total of 65 individual organizations that have been offered as choices. Therefore, some organizations have been selected as the first (or second or third etc.) choice for a number of the employees. This data is summarized on a separate sheet - listing the organization names in rows and identifying how many employees selected that organization as the first, second, third, fourth, or fifth choice.

For the organizations that have only one employee indicating that the organization is their first choice, I have successfully used VLOOKUP to pull the employee name.

What I have not been able to figure out is whether or not it is possible to use VLOOKUP, (possibly in combination with IF, OR, AND or some other FUNCTION or combination of FUNCTIONS) to pull the names of all the employees who have identified a particular organization as their first (or second or third etc.,) choice. That is, my current formula, search the name column pulls the first name that completes the logical test and stops. What I need is a formula that continues checking the entire column and pulls all the names that complete the test.

Is that possible?

Any assistance would be greatly appreciated.

Thanks!

Improve this question
3
  • 1
    Yes it is possible with worksheet functions.
    – Scott Craner
    Commented Nov 9, 2017 at 20:28
  • Are you able to suggest either a formula or the name(s) of the function(s) you believe would accomplish this?
    – CFE Data
    Commented Nov 9, 2017 at 22:10
  • 1
    Not without a lot more info, including but not limited to; test data, expected outcomes, and what formulas you have tried.
    – Scott Craner
    Commented Nov 9, 2017 at 22:11

1 Answer 1

Reset to default
1

@CFE Data since you have not uploaded the database so I've assumed as per your descriptions and would like to suggest solution which filters all the names have similar choice for Organization 1.

enter image description here

For this example my data range is A1 to C6.

Filter criteria is in cell E2.

Results are in Cell E4 downwards.

Formula is in cell E4 which is,

{=IFERROR(INDEX($A$2:$A$6,SMALL(IF($E$2=$B$2:$B$6,ROW($A$2:$A$6)-1,""),ROW()-3)),"")}

NB: Finish the Formula with Ctrl+Shift+Enter.

Org1 stands for Organization & Ch is choice. Remember in formula Row()-3 determines position of the first name which is at E4 (Manish).

So you need to adjust the data range as per your data structure and requirements.

Improve this answer
7
  • Thank-you very much Rajesh - I greatly appreciate your help. I should have been more clear in my initial question. Below please see a sample of the data I am using:Name 1st Ch 2nd Ch 3rd Ch 4th Ch 5th Ch Kiran Org11 Org45 Org22 Org6 Org31 Manish Org16 Org45 Org15 Org2 Org12 Rajeev Org11 Org33 Org22 Org2 Org13 Ravi Org16 Org33 Org14 Org42 Org37 Sanjay Org11 Org27 Org14 Org36 Org52
    – CFE Data
    Commented Nov 12, 2017 at 3:54
  • Thanks you very much Rajesh - your help is greatly appreciated. I should have included a sample of my data. I have done so below: Name 1st Ch 2nd Ch 3rd Ch 4th Ch 5th Ch Kiran Org11 Org45 Org22 Org6 Org31 Manish Org16 Org45 Org15 Org2 Org12 Rajeev Org11 Org33 Org22 Org2 Org13 Ravi Org16 Org33 Org14 Org42 Org37 Sanjay Org11 Org27 Org14 Org36 Org52
    – CFE Data
    Commented Nov 12, 2017 at 3:58
  • CFE,,, its really wonderful that you got the solution you were looking for also, plz don't forget to VOTE my answer.
    – Rajesh Sinha
    Commented Nov 12, 2017 at 6:45
  • Hello again Rajesh, - I did vote for your answer but I get a pop-up that says my reputation rating is less than 15 so the vote is recorded but not publicly displayed. At any rate, I am wondering if you would be so kind as to look at the data table I will post in a second comment. It shows how my data is organized and as you will see, unlike your solution which had the organizations in the columns, and the choices in the rows, I have the choices in the columns and the organizations that they have selected in the rows. As a result, I am not able yet to quite get the formula I require. Thank-you!
    – CFE Data
    Commented Nov 12, 2017 at 22:17
  • Name 1st Ch 2nd Ch 3rd Ch 4th Ch 5th Ch Kiran Org11 Org45 Org22 Org6 Org31 Manish Org16 Org45 Org15 Org2 Org12 Rajeev Org11 Org33 Org22 Org2 Org13 Ravi Org16 Org33 Org14 Org42 Org37 Sanjay Org11 Org27 Org14 Org36 Org52
    – CFE Data
    Commented Nov 12, 2017 at 22:23

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .