When is vlookup formula used in excel

Thank you! Any more feedback? The more you tell us the more we can help. Can you help us improve? Resolved my issue. Clear instructions. Easy to follow. No jargon. Pictures helped. Didn't match my screen. Incorrect instructions.

Too technical. Not enough information. Not enough pictures. Any additional feedback? Submit feedback. If you know the name of a product, for instance, and you want to quickly determine its price, simply enter the product name into Excel and VLOOKUP will find the price for you. Specify the cell in which you will enter the value whose data you're looking for. In this case, we've selected the entire table excluding the headers. Enter the value whose data you're searching for.

In this example, the third parameter is 2. This means that the second column in the table is where we will find the value to return. Since the table range is set to A1:B6, the return value will be in the second column somewhere in the range B1:B6. This parameter determines whether you are looking for an exact match or approximate match.

A parameter of TRUE means that a "close" match will be returned. To find an approximate match, use TRUE as the final parameter. Let's lookup a value that does not exist in our data to demonstrate the importance of this parameter! If no match is found, it returns the next smaller value which in this case is "Apples". Let's modify our example above and assume that the table is in a different Sheet called Sheet2 in the range A1:B6.

By preceding the table range with the sheet name and an exclamation mark, we can update our VLOOKUP to reference a table on another sheet. Let's throw in one more complication. What happens if your sheet name contains spaces? If there are spaces in the sheet name, you will need to change the formula further. Let's assume that the table is on a Sheet called "Test Sheet" in the range A1:B6, now we need to wrap the Sheet name in single quotes as follows:.

For example, if you wanted to have the table portion of the VLOOKUP formula be from an external workbook, we could try the following formula:.