HLOOKUP Function





※ Download: V lookup h lookup in excel


The VLOOKUP and HLOOKUP combined formula was shown with an example. Instead of VLOOKUP and HLOOKUP, we can also use the INDEX and MATCH functions based on different operations. Use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows.


This parameter determines whether you are looking for an exact match or approximate match. Example as Worksheet Function Let's explore how to use VLOOKUP as a worksheet function in Microsoft Excel. Hope you will like this article.


How to Use VLOOKUP and HLOOKUP Functions in Excel 2016 - VLOOKUP and HLOOKUP However, VLOOKUP and HLOOKUP functions have certain drawbacks that could lead to potential errors, especially for users who are often unfamiliar with the spreadsheet or financial model. When you're comfortable with VLOOKUP, the HLOOKUP function is equally easy to use.


It can be used as a worksheet function WS in Excel. As a worksheet function, the VLOOKUP function can be entered as part of a formula in a cell of a worksheet. The first column is 1. Enter FALSE to find an exact match. Enter TRUE to find an approximate match. If this parameter is omitted, TRUE is the default. Returns The VLOOKUP function returns any datatype such as a string, numeric, date, etc. Example as Worksheet Function Let's explore how to use VLOOKUP as a worksheet function in Microsoft Excel. First Parameter The first parameter in the VLOOKUP function is the value to search for in the table of data. In this example, the first parameter is 10251. This is the value that the VLOOKUP will search for in the first column of the table of data. Because it is a numeric value, you can just enter the number. In this example, the second parameter is A1:B6 which gives us two columns to data to use in the vertical lookup - A1:A6 and B1:B6. The first column in the range A1:A6 is used to search for the Order value of 10251. The second column in the range B1:B6 contains the value to return which is the Product value. Third Parameter The third parameter is the position number in the table where the return data can be found. A value of 1 indicates the first column in the table. The second column is 2, and so on. 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. Fourth Parameter Finally and most importantly is the fourth or last parameter in the VLOOKUP. This parameter determines whether you are looking for an exact match or approximate match. In this example, the fourth parameter is FALSE. A parameter of FALSE means that VLOOKUP is looking for an EXACT match for the value of 10251. Since the VLOOKUP is able to find the value of 10251 in the range A1:A6, it returns the corresponding value from B1:B6 which is Pears. Approximate Match To find an exact match, use FALSE as the final parameter. 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! VLOOKUP from Another Sheet You can use the VLOOKUP to lookup a value when the table is on another sheet. Let's modify our example above and assume that the table is in a different Sheet called Sheet2 in the range A1:B6. A1:B6, 2, FALSE 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. VLOOKUP from Another Sheet with Spaces in Sheet Name 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. A1:B6, 2, FALSE By placing the sheet name within single quotes, we can handle a sheet name with spaces in the VLOOKUP function. VLOOKUP from Another Workbook You can use the VLOOKUP to lookup a value in another workbook. Why use Absolute Referencing? Now it is important for us to cover one more mistake that is commonly made. When people use the VLOOKUP function, they commonly use relative referencing for the table range like we did in some of our examples above. This will return the right answer, but what happens when you copy the formula to another cell? The table range will be adjusted by Excel and change relative to where you paste the new formula. This is a great way to spruce up your spreadsheet so that you don't see traditional Excel errors.

 


HLOOKUP is a very useful function for creating horizontal lookups, but as most of the tables that we deal with are vertical hence this function is not very popular. We also cover this topic in our. For finding the result. This document explains the functions of VLOOKUP and HLOOKUP and how to use them in a spreadsheet. We will be using an example to find out a specific result using the combination of VLOOKUP and HLOOKUP. Remember HLOOKUP function in Excel can return only one value. A logical value that specifies whether you want HLOOKUP to find an exact match or an approximate match. This is the value that the VLOOKUP will search for in the first column of the table of data.