Double Xlookup & Data Validation

[EWRUCK]

Hi, I have a data validation for a competency selection. Which then limits the selection on sub competency you can choose based on that drop down. I'm using List and =Indirect in that data validation. Under that there are three ratings, Descriptions, Meets expectations and exceeds expecations. I'm struggling to get those to be dependent on those drop downs without writing extensive if formulas. I have tried double xlookups with and without data validation. I have reformatted the data to switch the columns and rows to align in the as the chart is.

Specifically looking at C12 -D15. There is some extra in here, this is the first I have used the XL2bb.

Take 2 WW PE Scoring_Managers.xlsx
	B	C	D
1
2
3
4
5		Interpersonal	Engagement
6		Description	Employee mobilizes both the organization’s and community resources to raise the visibility of community issues; increase individual and organizational commitment to resolving those issues; develop new solutions to old problems; gain new resources or make better use of existing resources to improve outcomes for members of the community.
7		Meets Expectations	Employee mobilizes both the organization’s and community resources to raise the visibility of community issues; increase individual and organizational commitment to resolving those issues; develop new solutions to old problems; gain new resources or make better use of existing resources to improve outcomes for members of the community.
8		Exceeds Expectations	Employee mobilizes both the organization’s and community resources to raise the visibility of community issues; increase individual and organizational commitment to resolving those issues; develop new solutions to old problems; gain new resources or make better use of existing resources to improve outcomes for members of the community.
9
10
11
12		Interpersonal	Engagement
13		Description	#N/A
14		Meets Expectations	#SPILL!
15		Exceeds Expectations	#N/A
16
17
18
19
Sheet1

Cell Formulas
Range	Formula
D6:F6	D6	=XLOOKUP(D5,'Competency Chart'!$A$2:$A$24,'Competency Chart'!$B$2:$D$24,XLOOKUP('Competency Chart'!$B$1:$D$1,Test!A11:B13,'Competency Chart'!$B$2:$D$24,),)
D7:F7	D7	=XLOOKUP(D5,'Competency Chart'!$A$2:$A$24,'Competency Chart'!$B$2:$D$24,XLOOKUP('Competency Chart'!$B$1:$D$1,Test!A12:B14,'Competency Chart'!$B$2:$D$24,),)
D8:F8	D8	=XLOOKUP(D5,'Competency Chart'!$A$2:$A$24,'Competency Chart'!$B$2:$D$24,XLOOKUP('Competency Chart'!$B$1:$D$1,Test!A13:B15,'Competency Chart'!$B$2:$D$24,),)
D13	D13	=XLOOKUP(D12,COMPETTBL[[#Headers],[Transparent Communication]:[Staff Management]],XLOOKUP(Sheet1!C13,COMPETTBL[Competencies],COMPETTBL[[Transparent Communication]:[Staff Management]]))
D14	D14	=XLOOKUP(XLOOKUP(D12,COMPETTBL[[#Headers],[Transparent Communication]:[Staff Management]],COMPETTBL[[Transparent Communication]:[Staff Management]]), Sheet1!C13,COMPETTBL[[Transparent Communication]:[Staff Management]])
D15	D15	=XLOOKUP(C15,COMPETTBL[Competencies],XLOOKUP(Sheet1!D12,COMPETTBL[[#Headers],[Transparent Communication]:[Staff Management]],COMPETTBL[[Transparent Communication]:[Staff Management]]))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Accountability	='Competency Chart'!$B$3:$D$3	D6:D8
Attendance___Punctuality	='Competency Chart'!$B$10:$D$10	D6:D8
Availability_Reliability	='Competency Chart'!$B$20:$D$20	D6:D8
Change_Management	='Competency Chart'!$B$22:$D$22	D6:D8
Collaboration_Team_Work	='Competency Chart'!$B$11:$D$11	D6:D8
Descriptions	='Competency Chart'!$B$2:$B$24	D6:D8
Documentation_and_Record_Keeping	='Competency Chart'!$B$13:$D$13	D6:D8
Engagement	='Competency Chart'!$B$8:$D$8	D6:D8
Equity	='Competency Chart'!$B$19:$D$19	D6:D8
Ethical_Leadership	='Competency Chart'!$B$5:$D$5	D6:D8
Exceeds_Expectations	='Competency Chart'!$D$2:$D$24	D6:D8
Fiscal_Management	='Competency Chart'!$B$23:$D$23	D6:D8
Flexibility	='Competency Chart'!$B$9:$D$9	D6:D8
Initiative	='Competency Chart'!$B$7:$D$7	D6:D8
Meets_Expectations	='Competency Chart'!$C$2:$C$24	D6:D8
Professional__Development	='Competency Chart'!$B$12:$D$12	D6:D8
Project_Management	='Competency Chart'!$B$21:$D$21	D6:D8
Quality_of_Work	='Competency Chart'!$B$16:$D$16	D6:D8
Quantity_of_Work	='Competency Chart'!$B$17:$D$17	D6:D8
Respect	='Competency Chart'!$B$4:$D$4	D6:D8
Safety__Equipment_Training	='Competency Chart'!$B$15:$D$15	D6:D8
Self___Time_Management	='Competency Chart'!$B$18:$D$18	D6:D8
Service	='Competency Chart'!$B$6:$D$6	D6:D8
Staff_Management	='Competency Chart'!$B$24:$D$24	D6:D8
Technical_Expertise	='Competency Chart'!$B$14:$D$14	D6:D8
Transparent_Communication	='Competency Chart'!$B$2:$D$2	D6:D8

Cells with Data Validation
Cell	Allow	Criteria
C5	List	=Lists!$A$2:$A$5
D5	List	=INDIRECT($C$5)
C12	List	=Lists!$A$2:$A$5
D12	List	=INDIRECT($C$12)

Lambda Functions
Name	Formula
DDL	=LAMBDA(range,[lookup1],[lookup2],[lookup3],[lookup4],[lookup5],[lookup6],[lookup7],[lookup8],[lookup9],[lookup10],LET(_s, "%^&&@", lookupValue, lookup1 & _s & lookup2 & _s & lookup3 & _s & lookup4 & _s & lookup5 & _s & lookup6 & _s & lookup7 & _s & lookup8 & _s & lookup9 & _s & lookup10, levelIndex, IFERROR(ROWS(TEXTSPLIT(lookupValue, , _s, TRUE())), 0) + 1, lookupArray, BYROW(EXPAND(CHOOSECOLS(range, SEQUENCE(1, levelIndex - 1)), , 10, ""), LAMBDA(row, TEXTJOIN(_s, FALSE, row))), returnRange, INDEX(range, 0, levelIndex), result, IF(ISOMITTED(lookup1) * levelIndex = 1, returnRange, XLOOKUP(lookupValue, lookupArray, returnRange):XLOOKUP(lookupValue, lookupArray, returnRange, , , -1)), result))

 

[StephenCrump]

No problem. I'm glad we could help.