Return Multiple Values for one Lookup

MINAX

New Member
Joined
Jul 28, 2015
Messages
2
Hello all,

I have tried all sorts of ways to get this puppy to work but come up shorthanded each time.

Firstly let me explain what I am trying to accomplish.

I have an excel spreadsheet - 2 tabs. One tab is "Agent Sheet" in which I have columns:
Date, Agent Name, Code, Time, Comments, Occurrence Points

On tab 1, I need to find a way so that I can write an agents name and have it return below each row that contains their name on the "agent sheet" tab.
The endgame is a way to keep track of attendance points.

Firstly I tried the following resource:
Vlookup Multiple Values - Return MULTIPLE corresponding values for ONE Lookup Value

It would return most of the correct rows but always throws in a row that doesn't belong to that agent.
Also using that formula provided in each of the columns I am looking to pull over really slowed down the spreadsheet. When changing the lookup value field (agent name to lookup) it would almost freeze the whole spreadsheet.

In summary this is what I am trying to achieve:
-Insert agent name into cell
-Below this cell a log appears that shows each time an agent was late, absent, etc.


Any help in getting me past that initial point where I'm stuck at would be very appreciated!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Excel 2012
ABCDEFGHIJKLMNOP
1DateAgent NameCodeTimeCommentsOccurrence PointsAgent Name -Agent Name3DateAgent NameCodeTimeCommentsOccurrence Points
2Date1Agent Name1Code1Time1Comments1Occurrence Points1Date3Agent Name3Code3Time3Comments3Occurrence Points3
3Date2Agent Name2Code2Time2Comments2Occurrence Points2
4Date3Agent Name3Code3Time3Comments3Occurrence Points3
5Date4Agent Name4Code4Time4Comments4Occurrence Points4
6Date5Agent Name5Code5Time5Comments5Occurrence Points5
7Date6Agent Name6Code6Time6Comments6Occurrence Points6
8Date7Agent Name7Code7Time7Comments7Occurrence Points7
9Date8Agent Name8Code8Time8Comments8Occurrence Points8
10Date9Agent Name9Code9Time9Comments9Occurrence Points9
Agent Sheet
Cell Formulas
RangeFormula
K2{=IFERROR(INDEX($A$2:$F$10,SMALL(IF($B$2:$B$10=$I$1,ROW($B$2:$B$10)-ROW($B$2)+1),ROWS($A$1:A1)),COLUMNS($A$1:A1)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Great, this seemed to work. My problem must be moving one part to another tab. I'll keep playing with it, but this got me started in the right direction. Thanks Shobolos!
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top