Happy month of love! Last year Microsoft rolled out a new Excel function that I have absolutely fallen in love with. Vlookup is something I use EVERY DAY, but there are some things that it just can’t do. Xlookup is the new and improved lookup that you’ve been waiting for. Lookups are great if you have a set of data, like names, on one sheet and a larger set of data with the same or similar set of names on another. If you need to fill the correlating data from your larger set onto your new set of data, a lookup can make it easy for you to do all at once instead of searching for your matching name and copying and pasting each line. For my example, I have my master data with my animals' names and information about each animal. My new sheet has a list of animal names and I only need the purchase price and the monthly feed cost pasted next to each name. The new list is in a different order and has some additional names, so copying and pasting the costs as a block of cells will not be accurate. Pasting each animal's information individually would take too long. I think the easiest way to use lookup functions is by clicking the functions button and filling in the prompts. You can also type the formula if you prefer. Once you select the Function Button, you will get a popup. If you have never used Xlookup before, you will need to type Xlookup in the search bar and click Go. Once you use it, it will show up in the bottom window as a recently used function. Once you select Xlookup, you’ll get a popup for the Function Arguments. This prompts each argument of the function which is why I like using the Function button instead of memorizing the formula. Here is what each field means: Here is what my popup looks like filled out for the results I’m looking for. I selected A2 which is the first name in my list, Bacon. I then selected the column that contains the matching names on my original list “Original!A:A.” For the return I selected what I want the lookup to return on my new sheet, the results of the lookup. The benefit of using Xlookup is that you can select multiple results to return in one lookup. I selected columns E and F on my original list. The 4th field is optional, but you can type a phrase or value to fill in if the results are not found. With a Vlookup, you get #N/A when there is no match. I typically add an IFERROR function paired with my Vlookup. With Xlookup, you can skip that extra step by building in the result you’d like displayed if no match is found. For my example, I want “Not Purchased” to display if any of the names on my new list are not on my original list. Here are the results I got. You can see both the Purchase Price and Monthly Feed Costs have pulled in from my original list for Bacon. In the Function preview, you can see the full formula. You can type this into B2 instead of going through the Function prompts if you’d like. I will format B2 and C2 as Currency since these are costs and I will copy the formula to the bottom of my data set by dragging the dot in the bottom right corner of the cell. The formulas have copied all the way down my data set and I have accurate costs filled in from my original list. Luka and Zoey were not on my original list, so my formula filled in “Not Purchased.” In my Vlookup tutorial, I suggested naming your data set so you don’t have to click back and forth between sheets. You can do the same thing here, but you’ll need to name the Lookup Array and the Return Array. For my example, I highlighted all of the names on my original list and named them “Search_Name.” Then, I highlighted all of the Purchase Price and Feed Cost cells and named those “Return.” Once you do that, you can just type in your set names on the function pop up instead of clicking around. Here’s what that looks like: This gives you the same results and may be faster for you to use; it’s just personal preference. This really opens up the possibilities with what you can do with a single lookup. There were previous ways to get these results with Vlookup, Hlookup, and Index Match, but now that it all lives in one place it makes it easier to remember and use successfully. If you have any issues or questions, please reach out by sending an email below!
0 Comments
Leave a Reply. |