When you have data in one place, and you want to collect a certain piece of that information and return it somewhere else based on a given value, you probably want a lookup.
Depending on the situation there are options about how to do that. The VLOOKUP (the V stands for Vertical) formula used to be the holy grail of excel formulas, if you had mastered that you truly knew what you were doing.
But, it some situations using INDEX and MATCH together makes more sense.
And, now there is XLOOKUP which has more flexibility and more options. More on that here.
HLOOKUP has been around for over 10 years and is the horizontal version of VLOOKUP.
XLOOKUP
Very briefly, the input required for the XLOOKUP formula in its most basic format is: what you are wanting to find (the value in C13 in our example), where will it be found (The list of first names: A2 to A8), What do you what to return (The matching value from B2 to B8 – i.e., the last name)
VLOOKUP
For VLOOKUP the structure is a bit different: what you are wanting to find (the value in C13 in our example), where will it be found (The lookup value needs to be in the left most column of the space you select here. You need to select both the input and output space), what is the column number of the data to return (In our example the last names are in column number 2). Only being able to lookup the left most column of the data is a big disadvantage of VLOOKUP. More information here.
INDEX MATCH
INDEX MATCH allows more flexibility. The input structure is a bit tricky, because we are using two functions, one inside the other.
INDEX: what are you trying to return, (something from B2 to B8 in our example), how far down the list is the specific item you what (This is where we have put the MATCH function). More information here.
MATCH: what you are wanting to find (the value in C13 in our example), where will it be found (The list of first names: A2 to A8). More information here.
The MATCH function returns the value 4 as Ajaz is the 4th name on the list. It passes this to the INDEX function which then returns the 4th name on the last name list.
Conclusion
If you have used VLOOKUP or HLOOKUP before, it is definitely worth exploring XLOOKUP the next time you want to use a lookup formula. If you are an INDEX MATCH fan, then give XLOOKUP a go. The increased flexibility it offers is fantastic.