Lookup value in table, return the team header above

Darren_workforce · Apr 22, 2024

With assistance, I have a formula that returns the supervisor based on the team member name. However, is it possible to reference the team member name and then return the specific team that member is on within the department? I have a cell range with all the team names (P6:P10) but is it possible to reference the agent name, look upwards at the first team header above their name, and return that result?

EX: Bob Smith would be CSR. Wanda Vasquez would be Tier 2.

supervisor lookup:

Excel Formula:

=IFERROR(INDEX(source!$B$2:$Z$2,1,MIN(IF(source!$B$3:$Z$60=A2,COLUMN(source!$B$2:$Z$2)-1))),"")

shift-del · Apr 22, 2024

Better change your structure.

Mappe5

A

B

C

1

Team

Role

Name

2

A

Supervisor

James

3

A

CSR

Mary Thomas

4

A

Tier 2

Alan Brown

5

A

Tier 3

Alex Henry

6

A

Lead

Elizabeth Buttons

Tabelle1

Domenic · Apr 22, 2024

First, in future, please post sample data as text, instead of an image. Or, alternatively, use the xl2BB add-in. Otherwise, we won't be able to copy/paste the data into a worksheet for testing purposes. Secondly, it would be a lot easier if you changed your layout, as @shift-del has suggested.

Now, with regards to your current layout, you could probably do something like this (Note that for simplicity, I've included the data and the results on one worksheet, and I've just made up some data. Change the references accordingly.)...

darren.xlsm

A

B

C

D

E

F

G

H

I

J

K

1

2

James (19A, 19T)

Sydney (19A, 19T)

Q

Sydney (19A, 19T)

Tier 2 (3)

3

4

5

CSR (8)

CSR (15)

6

A

N

7

B

O

8

C

P

9

10

11

12

13

14

Tier 2 (4)

15

D

16

E

17

F

18

G

19

Tier 3 (6)

20

H

21

I

Tier 2 (3)

22

J

Q

23

R

24

S

25

Lead (1)

26

Lead (1)

T

27

K

U

28

L

V

29

M

30

source

Cell Formulas
Range	Formula
J2	J2	=IFERROR(INDEX(source!$B$2:$G$2,1,SMALL(IF(source!$B$3:$G$60=I2,COLUMN(source!$B$2:$G$2)-COLUMN(source!$B$2)+1),1)),"")
K2	K2	=LET(col,INDEX(source!$B$3:$G$60,0,MATCH(J2,$B$2:$G$2,0)),INDEX(col,LARGE(IF(ROW(col)-MIN(ROW(col))+1<MATCH(I2,col,0),IF(ISNUMBER(SEARCH("(*)",col)),ROW(col)-MIN(ROW(col))+1)),1)))

Hope this helps!

Darren_workforce · Apr 22, 2024

shift-del said:
Better change your structure.

Mappe5
A B C
1 Team Role Name
2 A Supervisor James
3 A CSR Mary Thomas
4 A Tier 2 Alan Brown
5 A Tier 3 Alex Henry
6 A Lead Elizabeth Buttons
Tabelle1

Yes I'm sure that would alleviate the headache. I'm trying to work off of my supervisor's formatting so I had hoped to maintain the design as closely as possible and build around that

shift-del · Apr 22, 2024

If you want to lookup data then it is the best structure. And it is scalable.

Darren_workforce · Apr 22, 2024

Domenic said:
First, in future, please post sample data as text, instead of an image. Or, alternatively, use the xl2BB add-in. Otherwise, we won't be able to copy/paste the data into a worksheet for testing purposes. Secondly, it would be a lot easier if you changed your layout, as @shift-del has suggested.

Now, with regards to your current layout, you could probably do something like this (Note that for simplicity, I've included the data and the results on one worksheet, and I've just made up some data. Change the references accordingly.)...

darren.xlsm
A B C D E F G H I J K
1
2 James (19A, 19T) Sydney (19A, 19T) Q Sydney (19A, 19T) Tier 2 (3)
3
4
5 CSR (8) CSR (15)
6 A N
7 B O
8 C P
9
10
11
12
13
14 Tier 2 (4)
15 D
16 E
17 F
18 G
19 Tier 3 (6)
20 H
21 I Tier 2 (3)
22 J Q
23 R
24 S
25 Lead (1)
26 Lead (1) T
27 K U
28 L V
29 M
30
source
Cell Formulas
Range Formula
J2 J2 =IFERROR(INDEX(source!$B$2:$G$2,1,SMALL(IF(source!$B$3:$G$60=I2,COLUMN(source!$B$2:$G$2)-COLUMN(source!$B$2)+1),1)),"")
K2 K2 =LET(col,INDEX(source!$B$3:$G$60,0,MATCH(J2,$B$2:$G$2,0)),INDEX(col,LARGE(IF(ROW(col)-MIN(ROW(col))+1<MATCH(I2,col,0),IF(ISNUMBER(SEARCH("(*)",col)),ROW(col)-MIN(ROW(col))+1)),1)))

Hope this helps!

Going to work on this. As of now I'm getting a #NUM error for the K2 formula Not sure why. it works with the dummy info if I set everything up on 1 worksheet. But when I start referencing the other worksheet and putting in real names, it's not calculating the same thing and displaying an error instead. Also, not sure it matters, but the (19a, 19t) data is not the same cell as the supervisor name, it's technically a different cell. That's because of my copy/paste image. Sorry about that. I'd like to use xl2BB but IT has my system locked down and I can't use any add-ins.

Domenic · Apr 22, 2024

Can you post a representative sample of your data (the two supervisers) as text?

Darren_workforce · Apr 22, 2024

Domenic said:
Can you post a representative sample of your data (the two supervisers) as text?

The data below would all appear in the source tab. Disregard the numbers in parentheses as those are formulas that aren't part of this inquiry. I think the previous formulas work but I believe they're looking for data with numbers. If Kim Frost was referenced on the 'lookout' tab, it would fine her name, look directly up and reference the first "team header" it comes across (CSR in this case).

A	B	C	D	E	F	G
	James	(19a, 19t)			Sydney	(19a, 19t)


	CSR	(8)			CSR	(15)
	Mary Thomas				Brittany Black
	Joe Fleming				Aaron Alexander
	William Jenkins				Patty Ames
	Kim Frost				Angela Turner
	Jeff Sobchak				John Washington
	Alexis Warren				Heather Wright
	Perry Thorn				Candice Abbott
	Bob Smith				Lynn Lucas
	Tier 2	(4)			Michelle Nelson
	Whitney Jones				Tamara Davis
	Alan Brown				Devin Flores
	Joan bell				Tony Miller
	Karen Sanchez				Debbie Moore
	Tier 3	(6)			Rose Smith
	Wendy Smith				Gary Wright
	Alex Henry				Tier 2	(3)
	Christine James				Shannon Fuller
	Bill Benson				Sierra Ortiz
	Carla Williams				Wanda Vasquez
	Alicia Stone				Lead	(1)
	Lead	(1)			Sara Sutton
	Elizabeth Buttons

Domenic · Apr 22, 2024

The issue is that CSR (8), and others alike, actually resides in two separate cells, CSR in one and (8) in another. In that case, try the following instead...

darren.xlsm

A

B

C

D

E

F

G

H

I

J

K

1

James

(19a, 19t)

Sydney

(19a, 19t)

Sierra Ortiz

Sydney

Tier 2

2

3

4

CSR

(8)

CSR

(15)

5

Mary Thomas

Brittany Black

6

Joe Fleming

Aaron Alexander

7

William Jenkins

Patty Ames

8

Kim Frost

Angela Turner

9

Jeff Sobchak

John Washington

10

Alexis Warren

Heather Wright

11

Perry Thorn

Candice Abbott

12

Bob Smith

Lynn Lucas

13

Tier 2

(4)

Michelle Nelson

14

Whitney Jones

Tamara Davis

15

Alan Brown

Devin Flores

16

Joan bell

Tony Miller

17

Karen Sanchez

Debbie Moore

18

Tier 3

(6)

Rose Smith

19

Wendy Smith

Gary Wright

20

Alex Henry

Tier 2

(3)

21

Christine James

Shannon Fuller

22

Bill Benson

Sierra Ortiz

23

Carla Williams

Wanda Vasquez

24

Alicia Stone

Lead

(1)

25

Lead

(1)

Sara Sutton

26

Elizabeth Buttons

source

Cell Formulas
Range	Formula
J1	J1	=IF(LEN(I1)>0,IFERROR(INDEX(source!$B$1:$G$1,1,SMALL(IF(source!$B$2:$G$60=I1,COLUMN(source!$B$1:$G$1)-COLUMN(source!$B$1)+1),1)),""),"")
K1	K1	=IF(LEN(J1)>0,LET(col,INDEX(source!$B$2:$G$60,0,MATCH(J1,$B$1:$G$1,0)),INDEX(col,LARGE(IF(ROW(col)-MIN(ROW(col))+1<MATCH(I1,col,0),IF(ISNUMBER(MATCH(col,{"CSR","Tier 2","Tier 3","Lead"},0)),ROW(col)-MIN(ROW(col))+1)),1))),"")

Hope this helps!

Darren_workforce · Apr 22, 2024

Domenic said:
The issue is that CSR (8), and others alike, actually resides in two separate cells, CSR in one and (8) in another. In that case, try the following instead...

darren.xlsm
A B C D E F G H I J K
1 James (19a, 19t) Sydney (19a, 19t) Sierra Ortiz Sydney Tier 2
2
3
4 CSR (8) CSR (15)
5 Mary Thomas Brittany Black
6 Joe Fleming Aaron Alexander
7 William Jenkins Patty Ames
8 Kim Frost Angela Turner
9 Jeff Sobchak John Washington
10 Alexis Warren Heather Wright
11 Perry Thorn Candice Abbott
12 Bob Smith Lynn Lucas
13 Tier 2 (4) Michelle Nelson
14 Whitney Jones Tamara Davis
15 Alan Brown Devin Flores
16 Joan bell Tony Miller
17 Karen Sanchez Debbie Moore
18 Tier 3 (6) Rose Smith
19 Wendy Smith Gary Wright
20 Alex Henry Tier 2 (3)
21 Christine James Shannon Fuller
22 Bill Benson Sierra Ortiz
23 Carla Williams Wanda Vasquez
24 Alicia Stone Lead (1)
25 Lead (1) Sara Sutton
26 Elizabeth Buttons
source
Cell Formulas
Range Formula
J1 J1 =IF(LEN(I1)>0,IFERROR(INDEX(source!$B$1:$G$1,1,SMALL(IF(source!$B$2:$G$60=I1,COLUMN(source!$B$1:$G$1)-COLUMN(source!$B$1)+1),1)),""),"")
K1 K1 =IF(LEN(J1)>0,LET(col,INDEX(source!$B$2:$G$60,0,MATCH(J1,$B$1:$G$1,0)),INDEX(col,LARGE(IF(ROW(col)-MIN(ROW(col))+1<MATCH(I1,col,0),IF(ISNUMBER(MATCH(col,{"CSR","Tier 2","Tier 3","Lead"},0)),ROW(col)-MIN(ROW(col))+1)),1))),"")

Hope this helps!

That works like a charm! Thank you so much for sticking with it. Yes that's perfect and exactly what I was hoping to accomplish!!

Lookup value in table, return the team header above

Darren_workforce

Board Regular

Excel Facts

shift-del

Well-known Member

Domenic

MrExcel MVP

Darren_workforce

Board Regular

shift-del

Well-known Member

Darren_workforce

Board Regular

Domenic

MrExcel MVP

Darren_workforce

Board Regular

Domenic

MrExcel MVP

Darren_workforce

Board Regular

Similar threads

Forum statistics

Share this page

Lookup value in table, return the team header above

Board Regular

Excel Facts

Well-known Member

MrExcel MVP

Board Regular

Well-known Member

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock