by Melissa Esquibel, MCT, MOSM

Did you know that there are quite a few ways to look up data in Excel across worksheets and workbooks? Here’s a brief explanation of the top two ways and how they work.

VLookUp

Ah, VLookUp. To those who know it, this little function is a nugget of gold. To those who sort of know it, it can be extremely frustrating when it doesn’t behave as expected. But, to those who don’t know it, well, it’s like not having the right Allen wrench to put together your bed at your new apartment. OK, so let’s clear up a few things. VLookUp doesn’t do anything you don’t do when you’re looking up an item in a list. You know what you’re looking up, where you’re supposed to look for it, which column has the information you need, and what you’re going to do if you can’t find it. Here’s a menu.

If you want a Chopped Salad, and want to be sure you have enough money, you’d look down the list for Chopped Salad, then over to the second column for the price, $9.99. Now, let’s put that on an Excel worksheet.

The VLookUp formula would look like this: =VLookUp(D2,A2:B4,2,0)

• D2 is what you want to look up
• A2:B4 is where you want to look for it
• 2 is which column number has the information you want
• 0 answers what if you can’t find what I’m look for with an NA error message.

Index & Match

Yes, but, what if the look up column and the price column were switched? Index and Match could be used to get your answer. Index simply asks what range should it look in and which row number has the information needed. The “which row number” part is answered by a Match function. Match wants to know what to look up and where to look for it and, like a VLookUp, what to do if it can’t find it. What it returns is not the value, though, it’s the item number, so 2 in the following table, your menu choice, Chopped Salad would return 2 in the MATCH function.

My formula would look like this: =INDEX(A2:A4,MATCH(D2,B2:B4,0))

• A2:A4 is the range that the answer you need is located within
• D2 is your menu selection
• B2:B4 is the range in which your menu selection will be found
• 0 says look it up exactly

There’s more ways to look “this” up “there.” Join me at IAAP Summit for my Excel workshop on this subject.

Catch Melissa and other spectacular speakers at IAAP Summit 2019 in National Harbor, Maryland (just a short distance from Washington D.C.). Find out why IAAP Summit is the go-to conference for office and administrative professionals year-in and year-out.