Hello and welcome to A1 Excel! This post will be a quick reference guide on how to use various lookup formulas. This post will be referencing various formatting and formula details from past posts. I will try my best to reference the specific posts as we go in this guide to provide a more specific framework for how you can recreate/utilize these formulas on your own.
Before we get into the details for each formula here's a quick summary on how the lookup formulas work/quick tips to note for each one.
VLOOKUP:
In this example I am trying to add on another data specification to the vet clinic data set we've been working with over the past few posts.
Here I am using the VLOOKUP formula to search and insert location data for the vet clinic based on where the vet is located.
Example:
To note, you can specify the IFERROR comment to be anything you like. In this example I wrote it to have a specific command/note so that way whoever is looking over the dataset will know that a location needs to be added for any missing data.
Result:
Here is the formula utilized if you would like to use it for your own practice:
=IFERROR(VLOOKUP(E2,$A$13:$B$15,2,0),"New Vet-Look up Location Manually")
HLOOKUP:
In this example I want to get a specific count of Dog visits in 2018. The formula will search by a specific row (this is customizable depending on what you want to view).
Example:
Result:
To note, you can also add an IFERROR to the formula to account for any errors that may arise like in the VLOOKUP example above.
Here is the formula utilized if you would like to use it for your own practice:
=HLOOKUP("Dogs", A2:D5, 2, TRUE)
XLOOKUP:
This is the latest lookup style formula that has come out. It works like any lookup formula except it doesn't have to be tied to a left to right lookup like in VLOOKUP or be tied to a specific horizontal view like in HLOOKUP. The only drawback is that it is not available on Excel 2016 or Excel 2019. I'll show the VLOOKUP example as completed above in an XLOOKUP formula format.
Example:
Result:
To note, this formula does not require an IFERROR tacked on to the formula to get a customized return result.
Here is the formula utilized if you would like to use it for your own practice:
=XLOOKUP(E2,$A$13:$A$15,$B$13:$B$15,"Lookup new location",0,1)
The lookup formulas I listed are very versatile and customizable. In my own experience I've used VLOOKUP the most in a professional capacity and depending on the version of Excel your organization may have I would recommend using XLOOKUP as well.
Comentarios