Recently, my daughter, Sarah, broke her foot, and has had two surgeries, with orthopaedic specialists trying to put all the pieces back together.
I stayed with her for a few days after the latest surgery, to help her out.
One night, just as I was dozing off, I got a message on my iPhone. It was my daughter, texting from the next room.
- “Are you still awake?”
- “Yes, what do you need?”
- “I need help with Excel.”
That made me laugh, and I went in to see what help she needed.
Event Planning with Excel
Sarah is the event planner at Movember Canada, and is organizing launch parties for this year’s Movember events, across Canada.
“During November each year, Movember is responsible for the sprouting of moustaches on thousands of men’s faces, in Canada and around the world. With their “Mo’s”, these men raise vital funds and awareness for men’s health, specifically prostate cancer and male mental health initiatives.”
Of course, event planning is more difficult when you’re confined to your bed, but she’s been going non-stop, despite her injuries.
Apparently the surgical staff had to pry the phone from her hand in the last seconds before she was wheeled into the operating room. Now that’s job dedication!
Excel List of Invitees
Sarah had a list of invitees to the Movember events, and was trying to match email addresses with a short list of people who had not responded to their invitations.
The short list did not include the city name, and she wanted to pull that data from the original list.
Here, using some fake data that I created, is an example of the Excel worksheet, with the long list, and short list.
Solving Problem With Index and Match
Like many other Excel problems, this one can be solved with a combination INDEX/MATCH formula.
- The MATCH function finds the email in the original list, based on an exact match
- The INDEX function pulls the City from that email address’ row.
INDEX/MATCH Formula
In cell J4, I entered the following formula:
=INDEX($D$4:$D$1000,MATCH(I4,$E$4:$E$1000,0))
The formula looks for the city in column D, in the row where the matching email address was found in column E.
Copy Formula Down
Next, I copied the formula down to the last row in the short list, and all the cities showed up in the short list.
Finally, I copied the City cells in the short list, and pasted them as values, because the formulas weren’t needed any more.
Back to Sleep
It only took a couple of minutes to fix my daughter’s Excel problem, and she thanked me for coming to the rescue.
It warms a mother’s heart to know that her children grew up to be productive adults, who use Excel every day. Sarah might not remember that formula later, but that will give her a reason to call me the next time that she’s stuck in Excel.
After helping out, I headed back to the guest room, and fell asleep. I’m not sure how much longer Sarah kept working, but probably way too long.
And if you’re growing a moustache in support of Movember, please let me know!
_____________________