Podcast: Play in new window | Download
Welcome back to the podcast! This week’s edition: “Finding Your Perfect Match…by Using Excel Functions”
Well, it happened again. I’m almost positive I put both socks into the dryer, but only one came out. Tell me: where do the lost socks of the world go when they disappear? The Island of Misfit Socks? An exclusive club for singles? Maybe they have their own dating website, and they all go online in the hopes that they’ll find their perfect match. If so, I hope my argyle sock reads this:
Dear sock,
Your perfect match is here, in my dresser drawer, and it’s lonely. This is where you belong; come home.
What a frustrating experience. Sure, I could go buy a new pair, but then my original old sock would be a third wheel. The new pair of socks would try to have some time alone together and then this old sock would be hanging around and it would eventually just get awkward.
What I really wish is that life had something like Excel’s MATCH function. In case you’re not familiar, let me take a moment to explain it. If you have a worksheet that contains a column of data—let’s say, for example, articles of clothing—and another column that contains some or all of the same data, but in a different order, the MATCH function can help you determine which items appear in both columns. Not only that, it will also give you the row number in which the item appears in the first column. So if you know that one of the items in column B is “Sock,” and you want to know where that item can be found in column A, you can create a MATCH formula to tell you where in column A that item appears. It’s like map coordinates—better than GPS for misfit socks!
Once you have that formula mastered, you might want to try a more advanced formula like a VLOOKUP. This function will find a matching value in another set of data and return a value from an adjacent column. How does it work?
Well, let’s say you’re working in Excel and you have two worksheets of data. One contains Employee Names in column A and Employee IDs in column B. The second worksheet also has a series of Employee Names in column A, but they’re in a different order than the ones on the first worksheet. So if you want to find out the Employee IDs for these names, you can use the VLOOKUP function to look on the first worksheet for a match for each Employee Name on the second sheet, and it will return the correct Employee ID.
This can be extremely useful, especially in cases where you have thousands of records for which you need to return corresponding data.
There’s a lot you can do with Excel’s lookup functions if you know how to properly set them up. Here are links to some tips that will help you get started.
- Does This Match? – Using the MATCH function
- Vertical Challenge – Using the VLOOKUP function
- Look It Up – Using the LOOKUP function
- Data on the Horizon – Using the HLOOKUP function
- Show Me the Money – Using absolute references in formulas
- Go Away, #N/A – Checking for errors in formulas
- Pass or Fail? – Using VLOOKUP in a grading system
- Getting Clean Results – Nesting other functions in a VLOOKUP
You might also want to check out the tips in our previous podcast, Excel Hates Me! When 2+2 Isn’t Always 4 (VOL029), to make sure your data is clean. Otherwise, you might run into some errors when trying to find a match with these formulas.
Well, Excel’s lookup functions are really handy for locating data, but it looks like I’m going to have to hunt for this sock on my own. Shhh…be vewwy, vewwy quiet!
Thanks for joining us for this week’s podcast. Be sure to leave us a comment if there’s anything you’d like to see featured in a future episode.
And don’t forget that you can follow us on Twitter @productiveUcast, and you can also find the podcast on iTunes if you want to write a review.
See you next week!
Music provided by Clayton & Fulcrum
