Formula to validate the right manager within manager levels in multiple rows

Z

zinah

Active Member
Joined
Nov 28, 2018
Messages
368
Office Version
  1. 365
Platform
  1. Windows
Hi,
I have a list of managers and their management levels and what I need is to validate if each manager has the right management level. The issue is not all the managers have the same exact management levels, i.e. some managers have 1 level and others may have multiple.
I need a formula that can validate the last column of each row, the challenge is I have different column in each row, I put a simple formula for my table below and need to a better formula that can get the same result under desired result table, can anyone help please?
And what's the recommended formula that can replace the column "If False, what's the incorrect manager name?" in my table below?


Formula to validate Manager vs. Mgmet Level.xlsx
ABCDEFGHIJKLMN
1Desired Result Table
2Manager NameManagement Level 1Management Level 2Management Level 3Management Level 4Management Level 5Management Level 6Manager NameLast Management LevelIf False, what's the incorrect manager name?
3Manager 1Manager 30Manager 27Manager 1Manager 1TRUE
4Manager 2Manager 30Manager 27Manager 1Manager 7Manager 2Manager 2TRUE
5Manager 3Manager 30Manager 3Manager 3TRUE
6Manager 4Manager 30Manager 3Manager 1Manager 7Manager 2Manager 4Manager 4TRUE
7Manager 5Manager 30Manager 27Manager 5Manager 5TRUE
8Manager 6Manager 30Manager 3Manager 1Manager 6Manager 6TRUE
9Manager 7Manager 30Manager 8Manager 5Manager 6Manager 7FALSEManager 6
10Manager 8Manager 30Manager 8FALSEManager 30
11Manager 9Manager 30Manager 9FALSEManager 30
12Manager 10Manager 30Manager 10FALSEManager 30
13Manager 11Manager 30Manager 11FALSEManager 30
14Manager 12Manager 30Manager 12FALSEManager 30
15Manager 13Manager 30Manager 13FALSEManager 30
16Manager 14Manager 30Manager 14FALSEManager 30
17Manager 15Manager 30Manager 15FALSEManager 30
18Manager 16Manager 30Manager 16FALSEManager 30
19Manager 17Manager 30Manager 17FALSEManager 30
20Manager 18Manager 30Manager 18FALSEManager 30
21Manager 19Manager 30Manager 19FALSEManager 30
22Manager 20Manager 30Manager 20FALSEManager 30
23Manager 21Manager 30Manager 21FALSEManager 30
24Manager 22Manager 30Manager 22FALSEManager 30
25Manager 23Manager 30Manager 23FALSEManager 30
26Manager 24Manager 30Manager 24FALSEManager 30
27Manager 25Manager 30Manager 25FALSEManager 30
28Manager 26Manager 30Manager 26FALSEManager 30
29Manager 27Manager 30Manager 27FALSEManager 30
30Manager 28Manager 30Manager 8Manager 5Manager 28Manager 28TRUE
31Manager 29Manager 30Manager 29Manager 29TRUE
32Manager 30
33
Sheet1
Cell Formulas
RangeFormula
N9N9=E9
N10:N29N10=B10
M3,M7M3=L3=D3
M4M4=L4=F4
M5,M31M5=L5=C5
M6M6=L6=G6
M8:M9,M30M8=L8=E8
M10:M29M10=L10=B10
Cells with Conditional Formatting
CellConditionCell FormatStop If True
M3:M31Cell Value=FALSEtextNO
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
How about
Fluff.xlsm
ABCDEFGHIJKLM
1Desired Result Table
2Manager NameManagement Level 1Management Level 2Management Level 3Management Level 4Management Level 5Management Level 6Manager NameLast Management Level
3Manager 1Manager 30Manager 27Manager 1Manager 1TRUE
4Manager 2Manager 30Manager 27Manager 1Manager 7Manager 2Manager 2TRUE
5Manager 3Manager 30Manager 3Manager 3TRUE
6Manager 4Manager 30Manager 3Manager 1Manager 7Manager 2Manager 4Manager 4TRUE
7Manager 5Manager 30Manager 27Manager 5Manager 5TRUE
8Manager 6Manager 30Manager 3Manager 1Manager 6Manager 6TRUE
9Manager 7Manager 30Manager 8Manager 5Manager 6Manager 7Manager 6
10Manager 8Manager 30Manager 8Manager 30
11Manager 9Manager 30Manager 9Manager 30
12Manager 10Manager 30Manager 10Manager 30
13Manager 11Manager 30Manager 11Manager 30
14Manager 12Manager 30Manager 12Manager 30
15Manager 13Manager 30Manager 13Manager 30
16Manager 14Manager 30Manager 14Manager 30
17Manager 15Manager 30Manager 15Manager 30
18Manager 16Manager 30Manager 16Manager 30
19Manager 17Manager 30Manager 17Manager 30
20Manager 18Manager 30Manager 18Manager 30
21Manager 19Manager 30Manager 19Manager 30
22Manager 20Manager 30Manager 20Manager 30
23Manager 21Manager 30Manager 21Manager 30
24Manager 22Manager 30Manager 22Manager 30
25Manager 23Manager 30Manager 23Manager 30
26Manager 24Manager 30Manager 24Manager 30
27Manager 25Manager 30Manager 25Manager 30
28Manager 26Manager 30Manager 26Manager 30
29Manager 27Manager 30Manager 27Manager 30
30Manager 28Manager 30Manager 8Manager 5Manager 28Manager 28TRUE
31Manager 29Manager 30Manager 29Manager 29TRUE
32Manager 30Manager 30TRUE
Data
Cell Formulas
RangeFormula
M3:M32M3=LET(x,TAKE(FILTER(A3:G3,A3:G3<>""),,-1),IF(x=L3,TRUE,x))
 
Upvote 0
Solution
Fluff said:
How about
Fluff.xlsm
ABCDEFGHIJKLM
1Desired Result Table
2Manager NameManagement Level 1Management Level 2Management Level 3Management Level 4Management Level 5Management Level 6Manager NameLast Management Level
3Manager 1Manager 30Manager 27Manager 1Manager 1TRUE
4Manager 2Manager 30Manager 27Manager 1Manager 7Manager 2Manager 2TRUE
5Manager 3Manager 30Manager 3Manager 3TRUE
6Manager 4Manager 30Manager 3Manager 1Manager 7Manager 2Manager 4Manager 4TRUE
7Manager 5Manager 30Manager 27Manager 5Manager 5TRUE
8Manager 6Manager 30Manager 3Manager 1Manager 6Manager 6TRUE
9Manager 7Manager 30Manager 8Manager 5Manager 6Manager 7Manager 6
10Manager 8Manager 30Manager 8Manager 30
11Manager 9Manager 30Manager 9Manager 30
12Manager 10Manager 30Manager 10Manager 30
13Manager 11Manager 30Manager 11Manager 30
14Manager 12Manager 30Manager 12Manager 30
15Manager 13Manager 30Manager 13Manager 30
16Manager 14Manager 30Manager 14Manager 30
17Manager 15Manager 30Manager 15Manager 30
18Manager 16Manager 30Manager 16Manager 30
19Manager 17Manager 30Manager 17Manager 30
20Manager 18Manager 30Manager 18Manager 30
21Manager 19Manager 30Manager 19Manager 30
22Manager 20Manager 30Manager 20Manager 30
23Manager 21Manager 30Manager 21Manager 30
24Manager 22Manager 30Manager 22Manager 30
25Manager 23Manager 30Manager 23Manager 30
26Manager 24Manager 30Manager 24Manager 30
27Manager 25Manager 30Manager 25Manager 30
28Manager 26Manager 30Manager 26Manager 30
29Manager 27Manager 30Manager 27Manager 30
30Manager 28Manager 30Manager 8Manager 5Manager 28Manager 28TRUE
31Manager 29Manager 30Manager 29Manager 29TRUE
32Manager 30Manager 30TRUE
Data
Cell Formulas
RangeFormula
M3:M32M3=LET(x,TAKE(FILTER(A3:G3,A3:G3<>""),,-1),IF(x=L3,TRUE,x))
Click to expand...
Thank you so much, this is exactly what I needed.
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 1
You must log in or register to reply here.

Similar threads

B
  • Question Question
Sort/Filter based on SUMIF Value
2
Replies
13
Views
1K
Fluff
Fluff
excelbytes
  • Solved
COUNTIFS With Lists Of Multiple Criteria
Replies
5
Views
741
Fluff
Fluff
GMC The Macro Man
  • Question Question
Count with Multiple Criteria - Follow up
Replies
2
Views
210
myall_blues
myall_blues
P
  • Question Question
TypeScript (or other solutions) for modifying given list of cells (and similar problems)
Replies
0
Views
284
Pingaware
P
M
  • Question Question
VBA vs Formulas/Data Validation for Multiple Dependent Dropdown Lists
Replies
3
Views
2K
MissDB101
M

Forum statistics

Threads
1,224,813
Messages
6,181,117
Members
453,021
Latest member
Justyna P

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