Double Xlookup & Data Validation

EWRUCK

New Member
Joined
Nov 28, 2024
Messages
16
Office Version
  1. 365
Platform
  1. Windows
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.

Cell Formulas
RangeFormula
D6:F6D6=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:F7D7=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:F8D8=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,),)
D13D13=XLOOKUP(D12,COMPETTBL[[#Headers],[Transparent Communication]:[Staff Management]],XLOOKUP(Sheet1!C13,COMPETTBL[Competencies],COMPETTBL[[Transparent Communication]:[Staff Management]]))
D14D14=XLOOKUP(XLOOKUP(D12,COMPETTBL[[#Headers],[Transparent Communication]:[Staff Management]],COMPETTBL[[Transparent Communication]:[Staff Management]]), Sheet1!C13,COMPETTBL[[Transparent Communication]:[Staff Management]])
D15D15=XLOOKUP(C15,COMPETTBL[Competencies],XLOOKUP(Sheet1!D12,COMPETTBL[[#Headers],[Transparent Communication]:[Staff Management]],COMPETTBL[[Transparent Communication]:[Staff Management]]))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
Accountability='Competency Chart'!$B$3:$D$3D6:D8
Attendance___Punctuality='Competency Chart'!$B$10:$D$10D6:D8
Availability_Reliability='Competency Chart'!$B$20:$D$20D6:D8
Change_Management='Competency Chart'!$B$22:$D$22D6:D8
Collaboration_Team_Work='Competency Chart'!$B$11:$D$11D6:D8
Descriptions='Competency Chart'!$B$2:$B$24D6:D8
Documentation_and_Record_Keeping='Competency Chart'!$B$13:$D$13D6:D8
Engagement='Competency Chart'!$B$8:$D$8D6:D8
Equity='Competency Chart'!$B$19:$D$19D6:D8
Ethical_Leadership='Competency Chart'!$B$5:$D$5D6:D8
Exceeds_Expectations='Competency Chart'!$D$2:$D$24D6:D8
Fiscal_Management='Competency Chart'!$B$23:$D$23D6:D8
Flexibility='Competency Chart'!$B$9:$D$9D6:D8
Initiative='Competency Chart'!$B$7:$D$7D6:D8
Meets_Expectations='Competency Chart'!$C$2:$C$24D6:D8
Professional__Development='Competency Chart'!$B$12:$D$12D6:D8
Project_Management='Competency Chart'!$B$21:$D$21D6:D8
Quality_of_Work='Competency Chart'!$B$16:$D$16D6:D8
Quantity_of_Work='Competency Chart'!$B$17:$D$17D6:D8
Respect='Competency Chart'!$B$4:$D$4D6:D8
Safety__Equipment_Training='Competency Chart'!$B$15:$D$15D6:D8
Self___Time_Management='Competency Chart'!$B$18:$D$18D6:D8
Service='Competency Chart'!$B$6:$D$6D6:D8
Staff_Management='Competency Chart'!$B$24:$D$24D6:D8
Technical_Expertise='Competency Chart'!$B$14:$D$14D6:D8
Transparent_Communication='Competency Chart'!$B$2:$D$2D6:D8
Cells with Data Validation
CellAllowCriteria
C5List=Lists!$A$2:$A$5
D5List=INDIRECT($C$5)
C12List=Lists!$A$2:$A$5
D12List=INDIRECT($C$12)
Lambda Functions
NameFormula
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))
 
It sounds like the dependent dropdown part is working OK - with the multiple range names and INDIRECT? Did you know that with Excel 365, you now have the option of creating a single drop down list using the FILTER function?

Can you please post the layout of the relevant parts of the "Competency Chart" worksheet, and the COMPETTBL table?
 
Upvote 0
It sounds like the dependent dropdown part is working OK - with the multiple range names and INDIRECT? Did you know that with Excel 365, you now have the option of creating a single drop down list using the FILTER function?

Can you please post the layout of the relevant parts of the "Competency Chart" worksheet, and the COMPETTBL table?


Yes, I feel the dependent drop downs seem to be working and I was not aware of and though of using the Filter function for that.

This is the Compettbl that I have. The chart is transposed from this table. I didn't know if this would make a difference.


Take 2 WW PE Scoring_Managers.xlsx
ABCDE
1CompetenciesTransparent CommunicationAccountabilityRespectEthical Leadership
2DescriptionsEmployee effectively utilizes written, verbal and listening skills to communicate with staff, the public and all appropriate audiences. Supports decisions demonstrated through actions (verbal and nonverbal).Employee takes responsibility for own decisions, actions, results, complies with rules, regulations, ethics and policies and responsibilities entrusted to them. Holds self personally responsible for outcomes and how they are achieved. Employee is accountable for assuring prompt, accurate and effective service.Employee recognizes and appreciates one’s worth, abilities and differences. Treats all people with dignity, respect and fairness. Resolves interpersonal conflicts constructively. Shares time, energy and knowledge with others to ensure they can succeed. Demonstrates awareness and understanding of cultural and individual values.Employee leads with integrity, teamwork and commitment to a common goal while complying with rules, regulations, ethics and policies; maintains confidentiality, is honest and inspires the trust of others.
3 Meets ExpectationsAn employee meeting the expectations for communicating effectively shares important information in a timely manner with others and communicates in a clear and concise manner using appropriate grammar, pronunciation, and tone.An employee meeting the expectations for accountability complies with work hours, maintains good attendance, shows reliability in work habits, completes assignments and meets deadlines.An employee meeting the expectations for respect demonstrates appreciation; thanks others for their assistance. Demonstrates tact and diplomacy when resolving conflicts, addressing concerns and working with various constituencies; treats others with courtesy and respect.An employee meeting the expectations for ethical leadership avoids situations and actions considered inappropriate or which present a conflict of interest. This employee facilitates and contributes to the growth of others and abides by all Utility policies and procedures. Does not misrepresent self or use position or authority for personal gain.
4 Exceeds ExpectationsAn employee exceeding the expectations for communicating effectively listens carefully to others, asks questions for clarification, and ensures message is understood and demonstrates an ability to influence others by modeling appropriate body language and nonverbal communication.An employee exceeding the expectations for accountability demonstrates continuous improvement in work habits, asks for and uses feedback to improve performance problems; acknowledges and corrects mistakes.An employee exceeding the expectations for respect takes initiative to improve working relationships and foster feelings of mutual respect with coworkers and customers. This employee builds trust and creates a positive work environment through inclusion and supportive approaches to daily activities.An employee exceeding the expectations of ethical leadership adheres to a set of core values that are represented in decisions and actions. Motivates staff members and recognizes and rewards achievement. Employee understands all Utility policies and procedures, shares knowledge and communicates suggestions for policy and procedural changes.
Competency Table
 
Upvote 0
Thanks, but what you've posted is a simple 3 x 4 table. An XLOOKUP on one of the four headers will return the corresponding three table rows.

Your full layout sounds more complicated than this? But it's difficult to provide suggestions without understanding what this looks like.

Alternatively (and as long as you don't have personal or confidential information in your workbook) you could upload your workbook to a drop box and post the link here so we could take a look.
 
Upvote 0
Thanks, but what you've posted is a simple 3 x 4 table. An XLOOKUP on one of the four headers will return the corresponding three table rows.

Your full layout sounds more complicated than this? But it's difficult to provide suggestions without understanding what this looks like.

Alternatively (and as long as you don't have personal or confidential information in your workbook) you could upload your workbook to a drop box and post the link here so we could take a look.


I would think the xlookup would return the value as well but I'm wondering it if the drop down is what is causing the issues. I'm not sure if that is the hang up or if there is something else going on.

Here is the dropbox link:

 
Upvote 0
Thanks for this. A simple XLOOKUP is all that's required:

ABCDEFGHIJKLM
1Company Name
2Exempt Performance Appraisal
3
4Employee Name:Evaluated By:
5Date of Hire:Appraisal Date:
6Job Classification:Eval Period:
7
8EmergingDevelopingPerformingTransactionalTransformative
91 (Remedial)2 (Basic)3 (Competent)4 (Extensive)5 (Expert)
10Understands the objectives and roleCan perform aspects of objectivesProficiently performs consistentlyEnact positive changes inside of work groupEnact positive change outside of work group (Model, propose, design, and/or implement)
11
12Interpersonal EngagementSection Rating4
13DescriptionEmployee 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.
14Meets ExpectationsAn employee who meets the expectations for engagement with activities such as outreach events, community organizations, internal team building, working across departments. Promoting the organization, industry, and emerging professionals
15Exceeds ExpectationsAn employee who exceeds the expectations for engagement leverages their leadership skills of autonomy, growth, impact, and connection to engage staff and the public on a transformational level for the organization. This employee understands and promotes the organizational values, vision, and mission
16
17
18Technical Safety/ Equipment/TrainingSection Rating4
19DescriptionEmployee works safely with chemicals, and specialized equipment such as confined space, lock out tag out, gas meters, etc. Inspects safety equipment prior to use. Understands how the equipment is to be used. Comes to work with a safety conscious mind & attitude and maintains a clean work environment to be free of potential accidents.
20Meets ExpectationsAn employee who meets the safety needs of the organization preforms their job with minimal injuries occurred throughout year. Reports unsafe conditions to direct supervisor. Cleans up after work is complete and maintains a safe environment.
21Exceeds ExpectationsAn employee who exceeds the safety needs of the organization approaches each with safety conscious attitude and performs their job with no injuries occurred in the calendar year. Employee participates in safety inspections and correctly fills out all the necessary safety paperwork that pertains to the job. Employee collaborates with safety committee and help to create a safe working environment.
22
23etc etc ...
PE Compet Builder
Cell Formulas
RangeFormula
C13:C15,C19:C21C13=XLOOKUP(C12,COMPETTBL[#Headers],COMPETTBL)
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
B12List=Lists!$A$2:$A$5
C12List=INDIRECT($B$12)
B18List=Lists!$A$2:$A$5
C18List=INDIRECT($B$18)

where COMPETTBL looks like this:
ABCDEF
1CompetenciesTransparent Communicationetc etcEngagementSafety/ Equipment/Trainingetc etc etc
2DescriptionsEmployee effectively utilizes written, verbal and listening skills to communicate with staff, the public and all appropriate audiences. Supports decisions demonstrated through actions (verbal and nonverbal).Employee takes responsibility for own decisions, actions, results, complies with rules, regulations, ethics and policies and responsibilities entrusted to them. Holds self personally responsible for outcomes and how they are achieved. Employee is accountable for assuring prompt, accurate and effective service.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.Employee works safely with chemicals, and specialized equipment such as confined space, lock out tag out, gas meters, etc. Inspects safety equipment prior to use. Understands how the equipment is to be used. Comes to work with a safety conscious mind & attitude and maintains a clean work environment to be free of potential accidents.Employee produces work that is accurate, reliable, complete, thorough and effective. Results meet quality standards. Demonstrates quality outcomes including accuracy and thoroughness in work required for the position.
3 Meets ExpectationsAn employee meeting the expectations for communicating effectively shares important information in a timely manner with others and communicates in a clear and concise manner using appropriate grammar, pronunciation, and tone.An employee meeting the expectations for accountability complies with work hours, maintains good attendance, shows reliability in work habits, completes assignments and meets deadlines.An employee who meets the expectations for engagement with activities such as outreach events, community organizations, internal team building, working across departments. Promoting the organization, industry, and emerging professionalsAn employee who meets the safety needs of the organization preforms their job with minimal injuries occurred throughout year. Reports unsafe conditions to direct supervisor. Cleans up after work is complete and maintains a safe environment.An employee who meets the quality of work expectations plans and completes acceptable quality of work within deadlines and with attention to detail. Requires minimum oversight for quality outcomes and will ask or investigate about an assignment to ensure it is done correctly with attention to detail. Works with diligence and identifies opportunities to streamline or improve processes.
4 Exceeds ExpectationsAn employee exceeding the expectations for communicating effectively listens carefully to others, asks questions for clarification, and ensures message is understood and demonstrates an ability to influence others by modeling appropriate body language and nonverbal communication.An employee exceeding the expectations for accountability demonstrates continuous improvement in work habits, asks for and uses feedback to improve performance problems; acknowledges and corrects mistakes.An employee who exceeds the expectations for engagement leverages their leadership skills of autonomy, growth, impact, and connection to engage staff and the public on a transformational level for the organization. This employee understands and promotes the organizational values, vision, and missionAn employee who exceeds the safety needs of the organization approaches each with safety conscious attitude and performs their job with no injuries occurred in the calendar year. Employee participates in safety inspections and correctly fills out all the necessary safety paperwork that pertains to the job. Employee collaborates with safety committee and help to create a safe working environment.An employee who exceeds the quality of work expectations displays original thinking and creativity. Develops innovative approaches and ideas. Proposes new approaches, methods, or technologies for improving quality of work. Works with pride and develops better, less expensive ways to do things. Works cooperatively with others to produce innovative solutions.
Competency Table
 
Upvote 0
I see what you're saying here and this is great but when I adjust the formula I get a #SPILL Error. I must still be doing something wrong. Any suggetstions on that error?
 
Upvote 0
I see what you're saying here and this is great but when I adjust the formula I get a #SPILL Error. I must still be doing something wrong. Any suggetstions on that error?
Here is a link to what I updated for reference. I would think it is not far off of what you have but I feel like I've been staring at this and going around in circles. And possibly making it more complicated then what it needed to be.


 
Upvote 0
My formula will spill down three rows. So for example, you'll want:

In PE Compet Builder!C13: =XLOOKUP(C12,COMPETTBL[#Headers],COMPETTBL)

leaving C14 and C15 blank.
 
Upvote 0
Solution
My formula will spill down three rows. So for example, you'll want:

In PE Compet Builder!C13: =XLOOKUP(C12,COMPETTBL[#Headers],COMPETTBL)

leaving C14 and C15 blank.


Thank you so much. That is the one thing I didn't try. It works! I appreciate all the help on this. It's great to learn new techniques.
 
Upvote 0

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