Author: Michelle Jones (Page 1 of 40)

Srini’s Tech Tip: Compare Two Lists Using IF and VLOOKUP Functions

Excel lookup and conditional functions are essential in finding data and applying criteria to further accommodate conditions or results. Finding data can be tedious, but comparing two or more lists becomes cumbersome and arduous if done manually. You can use the common find feature to quickly find single piece of data, but it cannot be used to identify and highlight multiple pieces of data and on two separate lists.

Consider a circumstance where you have to compare two different lists and you are trying to find the unmatched items between those two lists. Suppose you have employees on Project A and Project B being tracked on two separate lists. The requirement is that both lists have to have either a unique name or a full name for the employee.  You may also use an alternate identifier if the name does not provide the uniqueness.

Problem/Task:

Identify employees in Project A that are not found in Project B.
Note:  See screenshots below for the two lists for Project A and Project B. 

Solution:

Use a combination of the VLOOKUP function and the IF function to identify the employees that are not found from Project A compared with Project B.
Added Task:  Highlight the employee names using conditional formatting

 

 

IF Function Syntax:

=IF (Logical_test,value_if_true,valued_if_false)

VLOOKUP function Syntax:

=VLOOKUP (Lookup_value,table_array,column_index_num,[range_lookup])

The range for List A is A4:G18, List B is J4:O13. However, the VLOOKUP function only needs to look up from column L, where the Full Name begins.

 

Brief Explanation for the VLOOKUP function:

The formula in column G (cell G4) in List A has a “nested” VLOOKUP function inside the IF function. The ISNA function further handles the error when the VLOOKUP cannot find a matching value in List B, and produces an “N/A” result.

The VLOOKUP function in cell G4 of List A looks up the value in cell C4 in the data area of List B, represented by $L$4:$O$13. The $ signs for the cell reference make the reference absolute, so that it does NOT change when copied to the other cells. The FALSE in the last argument in the VLOOKUP function produces an exact match for the lookup of the value in C4.

=IF(ISNA(VLOOKUP(C4,$L$4:$O$13,1,FALSE)),”Not on B”,””)

 

While the IF function can enter a value of “Not on B” using the nested VLOOKUP to determine the value, the conditional formatting feature in Excel highlights the cells in the desired color to make the results stand out.

 

** The IF and VLOOKUP functions are covered in our Excel Level 2 Intermediate course.

How to Handle a Condescending Colleague

What do you do about a colleague who is always telling you how to do your job? Or someone who seems to always have the answer, implying that you don’t? Or a coworker who uses a patronizing tone whenever they talk to you? Dealing with a condescending colleague can be frustrating, demoralizing, and even infuriating.

So how do you address their behavior in a way that protects you from its negative consequences and allows you to feel a sense of integrity? Try one of these strategies to keep your cool and not sink to the level of the offending party.

Don’t take it personally (even if it’s intentional). Lashing out to defend yourself will play right into the offending party’s hands. Instead, be calm, positive, and never underestimate the power of kindness in a negative situation. Remember that their behavior likely has more to do with them than it does with you and what they think of you.

Separate emotion from the message. Extract the information or feedback that is relevant to the task or situation and disregard the condescending tone. Avoid dishing out the same, even if it’s tempting to do so.

Ask for clarification. Some colleagues may come from a different workplace culture or be accustomed to speaking to others in a certain way that they don’t recognize as being inappropriate. They may literally be clueless, completely unaware of how they’ve offended you.

Neutralize your body language. Do your best to maintain a calm and neutral demeanor. Stand up straight, take up your space, don’t shrink back in offense, and hold your ground—both physically and mentally.

Choose your battles. Not every condescending remark requires a response. Distinguish between statements or actions that are irritating and behavior that is preventing you from getting your work done. There are times when it could be best to ignore their arrogance.

Address it calmly and professionally. If warranted, address bad office behavior by telling people when their actions are not okay with you. Calmly and professionally call out the behavior and let them know how it impacted you without making a scene or being dramatic.

Working with a condescending coworker is irritating at best and career-limiting at worst. But you don’t need to sit back and suffer. You can take steps to curb your colleague’s bluster or at least lessen its impact on you and your career.

 

References:
Harvard Business Review (2024, February 13) Amy Gallo: How to Deal with a Condescending Colleague
The Muse (2020, June 19) Lea McLeod: 4 Better Ways to Handle a Condescending Co-Worker Than Stooping to His Level 

 

Ask Yourself These Questions at the Midpoint of Your Career

You are in your career for the long haul, and the mid-career point is an opportunity for growth and self-care. It’s normal to wrestle with feelings of unmet expectations, missed opportunities, and paths not taken as you reach the midpoint of your career. Use this period to reevaluate your priorities, draw from your experiences, and carve out a path that aligns with your goals for the future.

Take a long, hard look at your work environment — the job role, expectations, culture, leadership, coworkers, growth opportunities and so on. Consider how you are feeling about those elements today, and try to remember how you felt about them before the mid-career fatigue set in.

  • What’s different now?
  • What’s changed?
  • Looking back on your career, what element allowed you to express your best self?
  • What aspects of your work gave you genuine enjoyment but have dwindled over the years?

Embarking on this “editing” process might seem overwhelming, even impossible, amid the busyness of life. But the perspective gained has immense benefits. When answering the above questions:

Tap into your purpose. Shift from a career shaped by external forces and others’ agendas to one driven by what you find meaningful. Identify what genuinely energizes your sense of purpose and let that guide your way forward.

Recognize the areas you have mastered. Reflect on the skills and knowledge you’ve acquired thus far in your career. Do not hold back! Consider how you might use them to fulfill your purpose, values, and priorities.

Determine what you want your days to look like. As you think big, don’t lose sight of the minutia. Get microscopic and consider what you want the particulars of your daily routine to look like.

Identify your current values and priorities. You may find that they’ve changed over time. Carefully consider which compromises you’re willing to make—and which ones you aren’t.

Remember that there’s no roadmap, we’re all different. The goal is to draw on the wisdom and experiences you’ve gained along the way, and consciously curate the next phase of your life. Re-discovering joy in the process is the key to finding your mid-career bliss.

 

References:
Harvard Business Review (2024, February 2) Rebecca Knight: 6 Questions to Ask at the Midpoint of Your Career
Medium (2023, December 21) John Horan: Mid-Career Journey – End of the Year Check-In

Supporting an Organizational Decision You Disagree With

In our work, there are times when we lead but there are also times when we must follow — and we may not always agree with the path we’re told to follow. We may feel “stuck” in anger, anxiety, and confusion.

Supporting a decision that you disagree with can be frustrating – especially when your team feels the same way. While you may wish you could change things, it is necessary to demonstrate resilience to your team and maintain a good relationship with management. But how can you lead convincingly, build trust, or create a space for healthy collaboration when you disagree with the fundamentals of the project? By decelerating and reflecting.

You can learn how to process difficult emotions, clearly articulate problems, identify potential upsides, broaden your perspective and develop empathy, and visualize what executing an unpopular decision will actually look like. In doing so, you can demonstrate a good attitude to your team, maintain a positive relationship with management, and display resilience by pivoting and working toward a new definition of success.

The reality is that you won’t agree with every organizational decision senior leadership makes. But you can make peace with a strategic direction you disagree with. Start by asking yourself a few questions.

What specific situation do I disagree with? Define exactly where the misalignment is happening—and why.

How will setting aside my disagreement benefit me? This encourages you to think about the silver lining, and any upsides of going along with an unpopular plan. For example, you might think “I get to keep the job I love, despite the organizational shakeup.”

How will setting aside my disagreement benefit my team? Once you identify your silver lining, it can be easier to see how others may be positively impacted.

What information might the decision-maker(s) have that I don’t? Extend some empathy and grace to your leaders. Perhaps there are factors at play that you’re not privy to. Considering these factors may quell your frustration.

What areas of alignment are there for making this decision? Make note of any commonalities between your initial work goals and the company’s goals.

By accepting this decision, what specific actions am I committing to take? You’re now ready to chart a new course of action. By addressing your emotions first, redirecting them to a place of positivity, and thinking beyond your own perspective, you can act with intention.

Whether or not you support your organization’s decision, your team must present their best effort in working to accomplish the goal. This means that despite your personal feelings or misgivings, you must insist that the team do their part. To achieve success, try the following suggestions.

  • Be as candid as you can be – without undermining the organization. If you bash the ideas openly or dismiss the goals, you are failing in your role within the organization and setting your team up for failure. You may be completely certain that your management has set you on the wrong path, but your reluctance in embracing the challenge means that any results you obtain will be called into question – especially if they prove you right.
  • Focus on collaborating and building consensus around the how. By centering the conversation around how you sidestep the tendency for teams to create a pros and cons list around the value of the goal itself.
  • Share as much information with your team as possible. Commit to becoming a source of information with your team and to being a conduit between the team you lead and the one you participate in. Fully sharing information about progress, and passing along questions is a critical feature of your role within the organization and your team is depending on you.
  • Remember to disagree and commit. You, and your team, want to be known as reliable and that means delivering what is expected regardless of how valuable you think it is.

There’s no doubt that this scenario will happen often throughout your management career. Knowing how to handle it without throwing others under the bus or crushing your credibility will distinguish you as a professional who can be relied upon as well as demonstrating that you are a team player.

 

References:
Harvard Business Review (2024, February 19) Cheryl Strauss Einhorn: How to Make Peace with a Company Decision You Don’t Like
Stewart Leadership Blog (2022) Peter Stewart: How to Lead a Team When You Disagree with the Direction

« Older posts