CODEWITHSUNDEEP
vbaexcel
DukeLuke
DukeLuke

Reputation: 315

Excel 2013 VLOOKUP() based on multiple criteria, retrieving multiple rows

I have an issue that can (I believe) be solved by just excel, and may won't require VBA (I could be wrong). I believe it can be solved by nested functions but the formula I've tried has not worked.

Here's my data:

 Name               Report #          Name
Mark Doe            ReportXXX       Mark Doe
Connie Doe          ReportYYY       Connie Doe
Debbie Doe          REPORTYYY       Debbie Doe  
Valerie Doe         FSMVALTR1       Valerie Doe
Jeff Doe            FSMVALTR1       Jeff Doe
Andy Doe            RAZXYBCA1       Andy Doe    
Ryan Doe            RAZXYBCA1       Ryan Doe    
Andy Doe            RAZ111111       Jill Doe    
Ryan Doe            RAZ222112       Amanda Doe

This list goes on for about ~4000 rows in the first NAME and REPORT # columns. In the second NAME column I have ~160 rows.

The second name column identifies all the users who actually use the report, with no duplicates. The two name and report # columns have many duplicates, since users have access to multiple reports, and many of them are the same report used for different purposes. Since the second NAME column has so few rows, the names don't match up all the way through, which can be observed near the bottom of both the NAME columns.

What I need to do is have a VLOOKUP that identifies the name in both of the columns and then returns the report number that each individual has access to across rows (horizontally), not down the columns. It also needs to I.D. numerous reports since individuals have access to anywhere from 1-15 reports, starting at the second and so on after the previous has been extracted.

Ideally it would look something like this:

Name          Report #      Name       ex column  ex column  ex column
Mark Doe      ReportXXX    Mark Doe    ReportXXX  ReportAAA   ReportB

I didn't list the other reports "Mark Doe" has access to and these would be somewhere down the long list of ~4000, along with his name repeated multiple times in the first NAME column, but the second "unique" name column would be where it is returning the reports to, across rows.

Upvotes: 0

Views: 622

Answers (2)

aFast
aFast

Reputation: 131

I made a method that uses additional three columns and it worked for me. I used the data you provided above.

Add three columns to the left.

  • in A2 enter =COUNTIFS($D$2:$D2,D2,$E$2:$E2,E2).
  • in B2 enter =SUMIFS($A$2:$A2,$A$2:$A2,1,$D$2:D2,D2).
  • in C2 enter =D2&B2.

Now copy those cells to all your 4000 rows.

Now next to the second name column add column headers numbered from 1 to as many reports you think can be the max one user can have (you said 15).

Now enter this vlookup in F2 =IFERROR(VLOOKUP($F2&G$1, your table array starting from column C as absolute/fixed ,3,FALSE),"") in my case it was =IFERROR(VLOOKUP($F2&G$1,$C$2:$E$11,3,FALSE),"").

Copy the formula accross all columns with number headers and down all rows that have names in second name column (160).

your sheet shuld look something like this image

All the best let me know if it worked for you.

Upvotes: 1

TheGuyThatDoesn'tKnowMuch
TheGuyThatDoesn'tKnowMuch

Reputation: 1250

Have you explored the option of using a Pivot Table?

The output would look like this:

enter image description here

With the Pivot Table setup like this:

enter image description here

This method would make for a cleaner view, as your current method looks to repeat ~4,000 times (once for each report & name combo). Hopefully this works for what you need.

Upvotes: 0

Related Questions