: Try using the new XLOOKUP function, an improved version of VLOOKUP that works in any direction and returns exact matches by default, making it easier and more convenient to use than its predecessor. This feature is only available if you have a Microsoft 365 subscription. If you are a Microsoft 365 subscriber, make sure you have the latest version of Office.
The basics of using VLOOKUP.
Use VLOOKUP
-
In the Formula Bar, type =VLOOKUP().
-
In the parentheses, enter your lookup value, followed by a comma. This can be an actual value, or a blank cell that will hold a value: (H2,
-
Enter your table array or lookup table, the range of data you want to search, and a comma: (H2,B3:F25,
-
Enter column index number. This is the column where you think the answers are, and it must be to the right of your lookup values: (H2,B3:F25,3,
-
Enter the range lookup value, either TRUE or FALSE. TRUE finds partial matches, FALSE finds exact matches. Your finished formula looks something like this: =VLOOKUP(H2,B3:F25,3,FALSE)
Want more?
When you need to find information in a large spreadsheet, or you are always looking for the same kind of information, use the VLOOKUP function.
VLOOKUP works a lot like a phone book, where you start with the piece of data you know, like someone's name, in order to find out what you don't know, like their phone number.
So, as an example, I'll enter part numbers, the thing I know, and find out Prices, the thing I don't know.
To do that, I'll click the cell where I want to see the Prices, I'll enter an = sign, VLOOKUP, and parentheses.
These parentheses will contain a set of arguments, and an argument is just a piece of data that the function needs in order to run.
I'll enter H2 as the first argument, because that is where I'll type the part numbers.
Follow that with a comma, and then, I'll enter the range of cells that contains the data I want to search. That's this block of data here.
The part numbers start in cell B3, and if I scroll down, you can see the status values end at cell E52.
So, I'll enter B3, a colon, and E52, then I'll type another comma.
And you need to do that because the functions won't work without the colons and commas.
Next, I'll type the number 3. This tells VLOOKUP that the values I want to see are in the third column from the left in the range of cells I want to search.
In other words, it's the third column over from the part numbers, the data I know.
Another comma, and I enter FALSE, because that gives me an exact match between part number and price.
And don't worry, I'll explain how that works later.
When I press Enter to tell Excel I am done, you can see I get an error message because I haven't entered a value in cell H2.
But, when I enter a part number, I get a price.
So what just happened? I told Excel, “Here is a value in the left-hand column of my data. Now look through this range of cells, and in the third column to the right, find the value on the same row.”
A lot like a phone book.
So up next, I'll explain each of the arguments - the values inside the parentheses - along with rules for using VLOOKUP.
Need more help?
Want more options?
Explore subscription benefits, browse training courses, learn how to secure your device, and more.
Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.
