Alternative to vlookup to find return the most senior role for a person from multiple options

C

Cantrecallmyusername

Board Regular
Joined
May 24, 2021
Messages
50
Office Version
  1. 365
Platform
  1. Windows
HI there,

I have a data file where I am looking to return the title for a person. The raw file is quite large and covers a long time period so for certain people they have multiple titles as they have been promoted.
Using Excel I would like to return the most senior title for each person where there is multiple values. Using a basic vlookup it returns the first value.

=VLOOKUP(A2,$A:$B,2,0)

Is there a formula that you could suggest that will find the last hit in the column for a vlookup?
It is possible to combine a number of IF statements with the vlookup formulas but there are 26 titles possible and I would like to stay clear of this.

In the below sample I would like Senior Manager 1 to be retuned for Employee. I will always want the last value in column B to be returned also for the employee.
FYI - there will be lots of employees in column A and more roles in column B

Book1
ABC
1EmployeeTitleWeek
2Donald DuckManager15/07/2022
3Donald DuckManager15/07/2022
4Donald DuckManager 122/07/2022
5Donald DuckManager15/07/2022
6Donald DuckManager 105/08/2022
7Donald DuckManager 122/07/2022
8Donald DuckManager15/07/2022
9Donald DuckManager15/07/2022
10Donald DuckManager 122/07/2022
11Donald DuckManager15/07/2022
12Donald DuckManager 122/07/2022
13Donald DuckManager15/07/2022
14Donald DuckManager15/07/2022
15Donald DuckManager 109/09/2022
16Donald DuckManager15/07/2022
17Donald DuckManager 122/07/2022
18Donald DuckManager 109/09/2022
19Donald DuckManager15/07/2022
20Donald DuckManager15/07/2022
21Donald DuckManager15/07/2022
22Donald DuckManager22/07/2022
23Donald DuckManager15/07/2022
24Donald DuckManager15/07/2022
25Donald DuckManager15/07/2022
26Donald DuckManager15/07/2022
27Donald DuckManager15/07/2022
28Donald DuckManager15/07/2022
29Donald DuckManager15/07/2022
30Donald DuckManager15/07/2022
31Donald DuckManager22/07/2022
32Donald DuckManager22/07/2022
33Donald DuckManager15/07/2022
34Donald DuckManager15/07/2022
35Donald DuckSenior Manager15/07/2022
36Donald DuckSenior Manager22/07/2022
37Donald DuckSenior Manager22/07/2022
38Donald DuckSenior Manager15/07/2022
39Donald DuckSenior Manager15/07/2022
40Donald DuckSenior Manager15/07/2022
41Donald DuckSenior Manager15/07/2022
42Donald DuckSenior Manager15/07/2022
43Donald DuckSenior Manager15/07/2022
44Donald DuckSenior Manager11/11/2022
45Donald DuckSenior Manager15/07/2022
46Donald DuckSenior Manager15/07/2022
47Donald DuckSenior Manager15/07/2022
48Donald DuckSenior Manager11/11/2022
49Donald DuckSenior Manager15/07/2022
50Donald DuckSenior Manager15/07/2022
51Donald DuckSenior Manager15/07/2022
52Donald DuckSenior Manager11/11/2022
53Donald DuckSenior Manager15/07/2022
54Donald DuckSenior Manager15/07/2022
55Donald DuckSenior Manager15/07/2022
56Donald DuckSenior Manager11/11/2022
57Donald DuckSenior Manager22/07/2022
58Donald DuckSenior Manager15/07/2022
59Donald DuckSenior Manager 115/07/2022
60Donald DuckSenior Manager 115/07/2022
61Donald DuckSenior Manager 111/11/2022
62Donald DuckSenior Manager 122/07/2022
63Donald DuckSenior Manager 122/07/2022
64Donald DuckSenior Manager 115/07/2022
65Donald DuckSenior Manager 115/07/2022
66Donald DuckSenior Manager 115/07/2022
67Donald DuckSenior Manager 111/11/2022
68Donald DuckSenior Manager 112/08/2022
69Donald DuckSenior Manager 115/07/2022
70Donald DuckSenior Manager 115/07/2022
71Donald DuckSenior Manager 115/07/2022
72Donald DuckSenior Manager 111/11/2022
73Donald DuckSenior Manager 122/07/2022
74Donald DuckSenior Manager 122/07/2022
75Donald DuckSenior Manager 115/07/2022
76Donald DuckSenior Manager 115/07/2022
77Donald DuckSenior Manager 115/07/2022
78Donald DuckSenior Manager 111/11/2022
79Donald DuckSenior Manager 122/07/2022
80Donald DuckSenior Manager 122/07/2022
81Donald DuckSenior Manager 115/07/2022
82Donald DuckSenior Manager 115/07/2022
83Donald DuckSenior Manager 115/07/2022
84Donald DuckSenior Manager 111/11/2022
85Donald DuckSenior Manager 122/07/2022
86Donald DuckSenior Manager 115/07/2022
87Donald DuckSenior Manager 115/07/2022
88Donald DuckSenior Manager 115/07/2022
89Donald DuckSenior Manager 111/11/2022
Sheet1
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
Cantrecallmyusername said:
.. find the last hit in the column for a vlookup?
Click to expand...
It is not entirely clear to me, but does this do what you want?

23 01 18.xlsm
ABCDEF
1EmployeeTitleWeekEmployeeDonald Duck
2Donald DuckManager15/07/2022Most Senior TitleSenior Manager 1
3Donald DuckManager15/07/2022
4Donald DuckManager 122/07/2022
5Donald DuckManager15/07/2022
6Donald DuckManager 15/08/2022
7Donald DuckManager 122/07/2022
8Donald DuckManager15/07/2022
9Donald DuckManager15/07/2022
10Donald DuckManager 122/07/2022
11Donald DuckManager15/07/2022
12Donald DuckManager 122/07/2022
13Donald DuckManager15/07/2022
14Donald DuckManager15/07/2022
15Donald DuckManager 19/09/2022
16Donald DuckManager15/07/2022
17Donald DuckManager 122/07/2022
18Donald DuckManager 19/09/2022
19Donald DuckManager15/07/2022
20Donald DuckManager15/07/2022
21Donald DuckManager15/07/2022
22Donald DuckManager22/07/2022
23Donald DuckManager15/07/2022
24Donald DuckManager15/07/2022
25Donald DuckManager15/07/2022
26Donald DuckManager15/07/2022
27Donald DuckManager15/07/2022
28Donald DuckManager15/07/2022
29Donald DuckManager15/07/2022
30Donald DuckManager15/07/2022
31Donald DuckManager22/07/2022
32Donald DuckManager22/07/2022
33Donald DuckManager15/07/2022
34Donald DuckManager15/07/2022
35Donald DuckSenior Manager15/07/2022
36Donald DuckSenior Manager22/07/2022
37Donald DuckSenior Manager22/07/2022
38Donald DuckSenior Manager15/07/2022
39Donald DuckSenior Manager15/07/2022
40Donald DuckSenior Manager15/07/2022
41Donald DuckSenior Manager15/07/2022
42Donald DuckSenior Manager15/07/2022
43Donald DuckSenior Manager15/07/2022
44Donald DuckSenior Manager11/11/2022
45Donald DuckSenior Manager15/07/2022
46Donald DuckSenior Manager15/07/2022
47Donald DuckSenior Manager15/07/2022
48Donald DuckSenior Manager11/11/2022
49Donald DuckSenior Manager15/07/2022
50Donald DuckSenior Manager15/07/2022
51Donald DuckSenior Manager15/07/2022
52Donald DuckSenior Manager11/11/2022
53Donald DuckSenior Manager15/07/2022
54Donald DuckSenior Manager15/07/2022
55Donald DuckSenior Manager15/07/2022
56Donald DuckSenior Manager11/11/2022
57Donald DuckSenior Manager22/07/2022
58Donald DuckSenior Manager15/07/2022
59Donald DuckSenior Manager 115/07/2022
60Donald DuckSenior Manager 115/07/2022
61Donald DuckSenior Manager 111/11/2022
62Donald DuckSenior Manager 122/07/2022
63Donald DuckSenior Manager 122/07/2022
64Donald DuckSenior Manager 115/07/2022
65Donald DuckSenior Manager 115/07/2022
66Donald DuckSenior Manager 115/07/2022
67Donald DuckSenior Manager 111/11/2022
68Donald DuckSenior Manager 112/08/2022
69Donald DuckSenior Manager 115/07/2022
70Donald DuckSenior Manager 115/07/2022
71Donald DuckSenior Manager 115/07/2022
72Donald DuckSenior Manager 111/11/2022
73Donald DuckSenior Manager 122/07/2022
74Donald DuckSenior Manager 122/07/2022
75Donald DuckSenior Manager 115/07/2022
76Donald DuckSenior Manager 115/07/2022
77Donald DuckSenior Manager 115/07/2022
78Donald DuckSenior Manager 111/11/2022
79Donald DuckSenior Manager 122/07/2022
80Donald DuckSenior Manager 122/07/2022
81Donald DuckSenior Manager 115/07/2022
82Donald DuckSenior Manager 115/07/2022
83Donald DuckSenior Manager 115/07/2022
84Donald DuckSenior Manager 111/11/2022
85Donald DuckSenior Manager 122/07/2022
86Donald DuckSenior Manager 115/07/2022
87Donald DuckSenior Manager 115/07/2022
88Donald DuckSenior Manager 115/07/2022
89Donald DuckSenior Manager 111/11/2022
Most Senior
Cell Formulas
RangeFormula
F2F2=INDEX(B:B,AGGREGATE(14,6,ROW(B2:B89)/(A1:A89=F1),1))
Named Ranges
NameRefers ToCells
'Most Senior'!_FilterDatabase='Most Senior'!$A$1:$C$89F2
 
Upvote 0
To get the last row a name occurs one, how about
Excel Formula: 
=XLOOKUP(A2,A2:A100,B2:B100,"",0,-1)
 
Upvote 0
Peter_SSs said:
It is not entirely clear to me, but does this do what you want?

23 01 18.xlsm
ABCDEF
1EmployeeTitleWeekEmployeeDonald Duck
2Donald DuckManager15/07/2022Most Senior TitleSenior Manager 1
3Donald DuckManager15/07/2022
4Donald DuckManager 122/07/2022
5Donald DuckManager15/07/2022
6Donald DuckManager 15/08/2022
7Donald DuckManager 122/07/2022
8Donald DuckManager15/07/2022
9Donald DuckManager15/07/2022
10Donald DuckManager 122/07/2022
11Donald DuckManager15/07/2022
12Donald DuckManager 122/07/2022
13Donald DuckManager15/07/2022
14Donald DuckManager15/07/2022
15Donald DuckManager 19/09/2022
16Donald DuckManager15/07/2022
17Donald DuckManager 122/07/2022
18Donald DuckManager 19/09/2022
19Donald DuckManager15/07/2022
20Donald DuckManager15/07/2022
21Donald DuckManager15/07/2022
22Donald DuckManager22/07/2022
23Donald DuckManager15/07/2022
24Donald DuckManager15/07/2022
25Donald DuckManager15/07/2022
26Donald DuckManager15/07/2022
27Donald DuckManager15/07/2022
28Donald DuckManager15/07/2022
29Donald DuckManager15/07/2022
30Donald DuckManager15/07/2022
31Donald DuckManager22/07/2022
32Donald DuckManager22/07/2022
33Donald DuckManager15/07/2022
34Donald DuckManager15/07/2022
35Donald DuckSenior Manager15/07/2022
36Donald DuckSenior Manager22/07/2022
37Donald DuckSenior Manager22/07/2022
38Donald DuckSenior Manager15/07/2022
39Donald DuckSenior Manager15/07/2022
40Donald DuckSenior Manager15/07/2022
41Donald DuckSenior Manager15/07/2022
42Donald DuckSenior Manager15/07/2022
43Donald DuckSenior Manager15/07/2022
44Donald DuckSenior Manager11/11/2022
45Donald DuckSenior Manager15/07/2022
46Donald DuckSenior Manager15/07/2022
47Donald DuckSenior Manager15/07/2022
48Donald DuckSenior Manager11/11/2022
49Donald DuckSenior Manager15/07/2022
50Donald DuckSenior Manager15/07/2022
51Donald DuckSenior Manager15/07/2022
52Donald DuckSenior Manager11/11/2022
53Donald DuckSenior Manager15/07/2022
54Donald DuckSenior Manager15/07/2022
55Donald DuckSenior Manager15/07/2022
56Donald DuckSenior Manager11/11/2022
57Donald DuckSenior Manager22/07/2022
58Donald DuckSenior Manager15/07/2022
59Donald DuckSenior Manager 115/07/2022
60Donald DuckSenior Manager 115/07/2022
61Donald DuckSenior Manager 111/11/2022
62Donald DuckSenior Manager 122/07/2022
63Donald DuckSenior Manager 122/07/2022
64Donald DuckSenior Manager 115/07/2022
65Donald DuckSenior Manager 115/07/2022
66Donald DuckSenior Manager 115/07/2022
67Donald DuckSenior Manager 111/11/2022
68Donald DuckSenior Manager 112/08/2022
69Donald DuckSenior Manager 115/07/2022
70Donald DuckSenior Manager 115/07/2022
71Donald DuckSenior Manager 115/07/2022
72Donald DuckSenior Manager 111/11/2022
73Donald DuckSenior Manager 122/07/2022
74Donald DuckSenior Manager 122/07/2022
75Donald DuckSenior Manager 115/07/2022
76Donald DuckSenior Manager 115/07/2022
77Donald DuckSenior Manager 115/07/2022
78Donald DuckSenior Manager 111/11/2022
79Donald DuckSenior Manager 122/07/2022
80Donald DuckSenior Manager 122/07/2022
81Donald DuckSenior Manager 115/07/2022
82Donald DuckSenior Manager 115/07/2022
83Donald DuckSenior Manager 115/07/2022
84Donald DuckSenior Manager 111/11/2022
85Donald DuckSenior Manager 122/07/2022
86Donald DuckSenior Manager 115/07/2022
87Donald DuckSenior Manager 115/07/2022
88Donald DuckSenior Manager 115/07/2022
89Donald DuckSenior Manager 111/11/2022
Most Senior
Cell Formulas
RangeFormula
F2F2=INDEX(B:B,AGGREGATE(14,6,ROW(B2:B89)/(A1:A89=F1),1))
Named Ranges
NameRefers ToCells
'Most Senior'!_FilterDatabase='Most Senior'!$A$1:$C$89F2
Click to expand...
This is a good solution however I have lots of examples and it would mean I would need to create a mapping table for each one and then run the formula - the Xlookup formula below removes this dependency from the formula - thanks for your suggestion though.
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
Cantrecallmyusername said:
This is a good solution however I have lots of examples and it would mean I would need to create a mapping table for each one and then run the formula - the Xlookup formula below removes this dependency from the formula - thanks for your suggestion though.
Click to expand...
No you wouldn't. I mis-understood and thought you just wanted a single cell to look up a single value. It seems you must want the most senior role listed down all the rows and Fluff's suggestion is certainly better than mine.

However, if that is the aim then you could get all those rows filled without even having to copy the formula down.
Note that I have changed the name in a few cells to show that it works for each name independently.
I have also hidden some rows to make the mini sheet smaller.

23 01 18.xlsm
ABCD
1EmployeeTitleWeek
2Donald DuckManager15/07/2022Senior Manager 1
3Donald DuckManager15/07/2022Senior Manager 1
4Donald DuckManager 122/07/2022Senior Manager 1
5Donald DuckManager15/07/2022Senior Manager 1
6Daisy DuckManager 15/08/2022Manager
7Daisy DuckManager 122/07/2022Manager
8Daisy DuckManager15/07/2022Manager
9Donald DuckManager15/07/2022Senior Manager 1
10Donald DuckManager 122/07/2022Senior Manager 1
11Donald DuckManager15/07/2022Senior Manager 1
12Donald DuckManager 122/07/2022Senior Manager 1
13Donald DuckManager15/07/2022Senior Manager 1
14Donald DuckManager15/07/2022Senior Manager 1
15Donald DuckManager 19/09/2022Senior Manager 1
16Donald DuckManager15/07/2022Senior Manager 1
17Donald DuckManager 122/07/2022Senior Manager 1
18Donald DuckManager 19/09/2022Senior Manager 1
19Donald DuckManager15/07/2022Senior Manager 1
33Donald DuckManager15/07/2022Senior Manager 1
34Donald DuckManager15/07/2022Senior Manager 1
35Donald DuckSenior Manager15/07/2022Senior Manager 1
36Donald DuckSenior Manager22/07/2022Senior Manager 1
56Donald DuckSenior Manager11/11/2022Senior Manager 1
57Donald DuckSenior Manager22/07/2022Senior Manager 1
58Donald DuckSenior Manager15/07/2022Senior Manager 1
59Donald DuckSenior Manager 115/07/2022Senior Manager 1
60Donald DuckSenior Manager 115/07/2022Senior Manager 1
87Donald DuckSenior Manager 115/07/2022Senior Manager 1
88Donald DuckSenior Manager 115/07/2022Senior Manager 1
89Donald DuckSenior Manager 111/11/2022Senior Manager 1
Most Senior (2)
Cell Formulas
RangeFormula
D2:D89D2=XLOOKUP(A2:A89,A2:A89,B2:B89,"",0,-1)
Dynamic array formulas.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
FILTER function to return ONLY records with most recent date
Replies
5
Views
388
Fluff
Fluff
B
  • Question
Delete content of cells / move up remaining data in multiple worksheets based on a range of blank cells in each worksheet
Replies
0
Views
326
Blanchetdb
B
C
  • Solved
FILTER function to return ONLY records with most recent date
Replies
6
Views
2K
Fluff
Fluff
J
  • Question
Count deadlines help
Replies
6
Views
448
juanam
J
E
  • Solved
DOUBLE FILTER function to return ONLY records with most recent date when base data is sorted continuously
2
Replies
11
Views
443
Fluff
Fluff

Forum statistics

Threads
1,214,522
Messages
6,120,019
Members
448,938
Latest member
Aaliya13

Share this page

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