# Skipping Through Look-Up Land

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.

 Lunch Menu Price Burger & Fries \$8.99 Chopped Salad \$9.99 Fish & Chips \$12.99

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.

 A B C D 1 Lunch Menu Price My Choice 2 Burger & Fries \$8.99 Chopped Salad 3 Chopped Salad \$9.99 4 Fish & Chips \$12.99

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.

 A B C D 1 Price Lunch Menu My Choice 2 \$8.99 Burger & Fries Chopped Salad 3 \$9.99 Chopped Salad 4 \$12.99 Fish & Chips

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