VB range lookup to return a value?!?

[Dmo9997]

Hi All -

I'm desperately hoping someone has the solution for what I'm trying to do here. Posting on mobile, so apologies in advance for not having code to paste outright.

I have two worksheets in my workbook, one titled teams and the other data. On sheet 'teams' I have a listing of agents and supervisors. Sups are listed starting at B1 through Y1, with their respective agents populating cells B2:Y32. Any agent in column B reports to the Sup in B1, etc.

Sheet 'data' contains something like 89000 rows of data about each individual...each agent name populating rows A2:A89000.

I need code to run a macro that looks at cell A2, looks that value (name) up in the array on sheet 'teams', and based on the column the value is found in, returns the value of that column, row 1 to sheet 'data', P2. Then I need the script to do that for every row on sheet 'data'. I have 25 Sups. 400 agents. And teams change monthly. So doing it manually or with a find/replace in the sheet itself MIGHT cause me to jump from a very high place.

I pray someone out there has the solution for this...I've tried every combination of if/then, v and h lookups, find/match, etc...I'm stuck.

 

[Dmo9997]

teams	A	B	C
1	John	John	Mary
2	Mary	agent1	agent5
3	Sue	agent2	agent6
4	Tom	agent3	agent7
5	Jane	agent4	agent8

<tbody>
</tbody>

This would be the teams table....names in bold are my Supervisors, agents are the team members

	A	B	C	D
1	Name	Hours Staffed	Claims Handled	Supervisor Name
2	agent1	Data Point1	Number1
3	agent1	Data Point2	Number2
4	agent3	Data Point3	Number3
5	agent4	Data Point4	Number4
6	agent3	Data Point5	Number5

<tbody>
</tbody>

And this is the table that I need to update. If agent1 appears in column A, row 2, a lookup should find agent1 under John, and populate D2 with "John". Then it should move forward to row 3 and perform the same check for D3, then D4 and so on.

 

[Eric W]

I see what you mean. You'd think that one of the lookup functions would work, but I couldn't figure it out. Creating a custom function was very easy though.

To do that, open your spreadsheet (make a backup first). Press ALT-F11 to open the Visual Basic Editor. On the left navigation pane, right click where it says VBAProject, then select Insert, and Module. When the window opens up, paste this:

Public Function GetSupervisor(agent)
Dim myagent as object

Set myagent = Sheets("teams").Cells.Find(agent)
If myagent Is Nothing Then
GetSupervisor = "??"
Else
GetSupervisor = Sheets("teams").Cells(1, myagent.Column)
End If
End Function


Now go back to your data sheet. In D2 type "=GetSupervisor(A2)". Then copy and paste D2 down the rest of column D.

-Eric

 

[Dmo9997]

That's Brilliant!!!! Worked like a charm!

I can't thank you enough....I'd tell you how many nights I lost sleep over this one, but you might be sorry you helped me if I did!