Select cell E4 and enter the VLOOKUP function shown below. Create two named ranges: Table1 and Table2. You can use the IF function to check whether a condition is met, and return one lookup table if TRUE and another lookup table if FALSE. So up next, I'll explain each of the arguments - the values inside the parentheses - along with rules for using VLOOKUP. When using the VLOOKUP function in Excel, you can have multiple lookup tables. In this tutorial you will learn how you can use vlookup on. Also one of the formulas that are least used because so many users are scared of it. The functionality of Excel is so comprehensive that average users don't even take advantage. vlookup is one of the most powerful formula in Excel. Now look through this range of cells, and in the third column to the right, find the value on the same row.” Microsoft Excel includes a variety of different functions that help users with calculations of any kind. So what just happened? I told Excel, “Here is a value in the left-hand column of my data.
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.īut, when I enter a part number, I get a price. When the user uses the vlookup function for finding specific information in an MS Excel spreadsheet, each matching information is displayed in the same row but in the next column. The Function Arguments box appears, prompting us for all the arguments (or parameters) needed in order to complete the VLOOKUP function. In other words, it's the third column over from the part numbers, the data I know.Īnother comma, and I enter FALSE, because that gives me an exact match between part number and price.Īnd don't worry, I'll explain how that works later. The first step to effectively using the VLOOKUP function is to make sure your data is well organized and suitable for using the function. The system would return us a list of all lookup-related functions in Excel. 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. So, I'll enter B3, a colon, and E52, then I'll type another comma.Īnd you need to do that because the functions won't work without the colons and commas. The part numbers start in cell B3, and if I scroll down, you can see the status values end at cell E52. I'll enter H2 as the first argument, because that is where I'll type the part numbers.įollow that with a comma, and then, I'll enter the range of cells that contains the data I want to search. 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. To do that, I'll click the cell where I want to see the Prices, I'll enter an = sign, VLOOKUP, and parentheses.
So, as an example, I'll enter part numbers, the thing I know, and find out Prices, the thing I don't know. 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.
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.