Ms excel: if function (WS)

MS Excel: HLOOKUP Function (WS)

This Excel tutorial explains how to use the Excel HLOOKUP function with syntax and examples. How to handle errors such as #N/A and retrieve the correct results is also discussed.

Description

The Microsoft Excel HLOOKUP function performs a horizontal lookup by searching for a value in the top row of the table and returning the value in the same column based on the index_number.

Syntax

The syntax for the HLOOKUP function in Microsoft Excel is:

HLOOKUP( value, table, index_number, [not_exact_match] )

Parameters or Arguments

value

The value to search for in the first row of the table.

table

Two or more rows of data that is sorted in ascending order.

index_number

The row number in table from which the matching value must be returned. The first row is 1.

not_exact_match

Optional. It determines if you are looking for an exact match based on value. Enter FALSE to find an exact match. Enter TRUE to find an approximate match, which means that if an exact match if not found, then it will look for the next largest value that is less than value. If this parameter is omitted, it will return an approximate match.

Note:

• 
  If index_number is less than 1, the HLOOKUP function will return #VALUE!.
  
• 
  If index_number is greater than the number of columns in table, the HLOOKUP function will return #REF!.
  
• 
  If you enter FALSE for the not_exact_match parameter and no exact match is found, then the HLOOKUP function will return #N/A.
  
• 
  See also the VLOOKUP function to perform a vertical lookup.
  

Applies To

The HLOOKUP function can be used in the following versions of Microsoft Excel:

• 
  Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000
  

Type of Excel Function

The HLOOKUP function can be used in Microsoft Excel as the following type of function:

• 
  Worksheet function (WS)
  

Example (as Worksheet Function)

Let's look at some Excel HLOOKUP function examples and explore how to use the HLOOKUP function as a worksheet function in Microsoft Excel:

Based on the spreadsheet above, the following Excel HLOOKUP function will return the following:

=HLOOKUP(10251, A1:G3, 2, FALSE)

Result: $16.80

This HLOOKUP example would return the value of $16.80. Let's take a closer look why.

First Parameter

The first parameter in the HLOOKUP function is the value to search for. So in this example, the HLOOKUP is searching for the value of 10251.

Second Parameter

The second parameter in the HLOOKUP function is the table which is set to the range of A1:G3. The HLOOKUP uses the first row in this range (ie: A1:G1) to search for the value of 10251.

Third Parameter

The third parameter is the index_number which is set to 2. This means that the second row in the table is where we will find the value to return. Since the table is set to A1:G3, the corresponding return value will be in A2:G2 (ie: second row as specified by the index_number of 2).

Fourth Parameter

Finally and most importantly is the fourth or last parameter in the HLOOKUP. In our example, it is set to FALSE. This means that you need to find an EXACT match for the value of 10251. We do not want to find a "close" match, but an EXACT match!! So if 10251 is not found in the range of A1:G1, then the HLOOKUP function should return #N/A.

Since the HLOOKUP is able to find the value of 10251 in the range A1:G1, it returns the corresponding value from A2:G2 which is $16.80.

Importance of Fourth Parameter

Let's further explore the importance of specifying TRUE vs FALSE for the last parameter in the HLOOKUP function.

So say we are looking for the Order ID of 10248, but as you can see, it is not in the range of A1:G1 in the spreadsheet above. Let's write our HLOOKUP formula with FALSE as the final parameter and another HLOOKUP formula with TRUE as the final parameter and see what happens.

=HLOOKUP(10248, A1:G3, 2, FALSE)

Result: #N/A

=HLOOKUP(10248, A1:G3, 2, TRUE)

Result: $14.00

The first HLOOKUP formula has FALSE specified as the final parameter. This means that the HLOOKUP is looking for an exact match for 10248. Since the value 10248 does not exist in the range A1:G1, the HLOOKUP function returns #N/A.

The second HLOOKUP formula has TRUE specified as the final parameter. This means that if an exact match if not found, the HLOOKUP function will look for the next largest value that is less than 10248. Now what does this mean to us?

First of all, it means that the data in A1:G3 MUST BE SORTED IN ASCENDING ORDER because the HLOOKUP is going to return the next largest value for 10248 and then stop searching. So if your data is not sorted in ascending order, you are going to get some really strange results.

Secondly, it means that the HLOOKUP function will find order 10247 as the approximate match. And therefore, return $14.00 as the result (the corresponding value from A2:G2).

Table in another sheet

Quite often we are asked the question, "What is an example of a HLOOKUP when the table is on another sheet?"

To answer that question, let's modify our example above and assume that the table is on Sheet2 in the range A1:G3.

We could rewrite our example as follows:

=HLOOKUP(10251, Sheet2!A1:G3, 2, FALSE)

By preceding the table range with the sheet name and an exclamation mark, we can update our HLOOKUP to reference a table on another sheet.

Table in another sheet with spaces in sheet name

Let's throw in one more complication, what happens if your Sheet name contains spaces, then you will need to change the formula further. So let's take a look at this case...

Let's assume that the table is on a Sheet called "Test Sheet" in the range A1:G3, we would need to modify our formula as follows:

=HLOOKUP(10251, 'Test Sheet'!A1:G3, 2, FALSE)

By placing the sheet name within single quotes, we can accommodate a sheet name with spaces in our HLOOKUP function.

Absolute Referencing

Now it is important for us to mention one more mistake that is commonly made. When people use the HLOOKUP function, they commonly use relative referencing for the table range like we did in 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. Let's explain further...

So if you had the following formula in cell J1:

=HLOOKUP(10251, A1:G3, 2, FALSE)

And then you copied this formula from cell J1 to cell K2, it would modify the HLOOKUP formula to this:

=HLOOKUP(10251, B2:H4, 2, FALSE)

Since your table is found in the range A1:G3 and not B2:H4, your formula would return erroneous results in cell K2. To ensure that your range is not changed, try referencing your table range using absolute referencing as follows:

=HLOOKUP(10251, $A$1:$G$3, 2, FALSE)

Now if you copy this formula to another cell, your table range will remain $A$1:$G$3.

How to Handle #N/A Errors

Finally, let's look at how to handle instances where the HLOOKUP function does not find a match and returns the #N/A error. In most cases, you don't want to see #N/A but would rather display a more user-friendly result.

For example, if you had the following formula:

=HLOOKUP(10251, $A$1:$G$3, 2, FALSE)

Instead of displaying #N/A error if you do not find a match, you could return the value "Not Found". To do this, you could modify your HLOOKUP formula as follows:

=IF(ISNA(HLOOKUP(10251, $A$1:$G$3, 2, FALSE)), "Not Found", HLOOKUP(10251, $A$1:$G$3, 2, FALSE))

This new formula will use the ISNA function to test if the HLOOKUP returns a #N/A error. If the HLOOKUP returns #N/A, then the formula will output "Not Found". Otherwise, it will perform the HLOOKUP as before.

This is a great way to spruce up your spreadsheet so that you don't see traditional Excel errors.

MS Excel: COUNTIF Function (WS)

This Excel tutorial explains how to use the Excel COUNTIF function with syntax and examples.

Description

The Microsoft Excel COUNTIF function counts the number of cells in a range, that meets a given criteria.

If you wish to apply multiple criteria, try using the COUNTIFS function.

Syntax

The syntax for the COUNTIF function in Microsoft Excel is:

COUNTIF( range, criteria )

Parameters or Arguments

range

The range of cells that you want to count based on the criteria.

criteria

The criteria used to determine which cells to count.

Applies To

The COUNTIF function can be used in the following versions of Microsoft Excel:

• 
  Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000
  

Type of Excel Function

The COUNTIF function can be used in Microsoft Excel as the following type of function:

• 
  Worksheet function (WS)
  

Example (as Worksheet Function)

Let's look at some Excel COUNTIF function examples and explore how to use the COUNTIF function as a worksheet function in Microsoft Excel:

Based on the Excel spreadsheet above, the following COUNTIF examples would return:

=COUNTIF(A2:A7, D2)

Result: 1

=COUNTIF(A:A, D2)

Result: 1

=COUNTIF(A2:A7, ">=2001")

Result: 4

Using Named Ranges

You can also use a named range in the COUNTIF function. For example, we've created a named range called family that refers to column A in Sheet 1.

Then we've entered the following data in Excel:

Based on the Excel spreadsheet above:

=COUNTIF(family, D2)

Result: 1

=COUNTIF(family, ">=2001")

Result: 4

To view named ranges: Under the Insert menu, select Name > Define.

Frequently Asked Questions

Question: I'm trying to use COUNTIF on a selection of cells (not necessarily one solid range), and the syntax of the function does not allow that. Is there another way to do this?

Here's an example of what I'd like to be able to do:

=COUNTIF(A2,A5,F6,G9,">0")

Answer: Unfortunately, the COUNTIF function does not support multiple ranges. However, you could try summing multiple COUNTIFs.

For example:

=SUM(COUNTIF(A2,">0"),COUNTIF(A5,">0"),COUNTIF(F6,">0"),COUNTIF(G9,">0"))

OR

=COUNTIF(A2,">0")+COUNTIF(A5,">0")+COUNTIF(F6,">0")+COUNTIF(G9,">0")

Question: I am using the COUNTIF function and I would like to make the criteria equal to a cell.

For example:

=COUNTIF(C4:C19,">=2/26/04")

I want to replace 2/26/04 with cell A1. How do I do this?

Answer: To use a cell reference in the criteria, you could do the following:

=COUNTIF(C4:C19,">="&A1)

Question:I would like to do the following:

=COUNTIF(ABS(A1:A10),">0")

i.e. count the number of values in the range A1:A10 that have a non-zero magnitude. The syntax I tried does not work. Could you help?

Answer: Because you can not apply the ABS function to range A1:A10, you will need to instead break up your formula into two COUNTIF functions as follows:

=COUNTIF(A1:A10,">0")+COUNTIF(A1:A10,"<0")

This will count the number of values that are either greater than 0 or less than 0.

MS Excel: HLOOKUP Function (WS)

This Excel tutorial explains how to use the Excel HLOOKUP function with syntax and examples. How to handle errors such as #N/A and retrieve the correct results is also discussed.

Description

The Microsoft Excel HLOOKUP function performs a horizontal lookup by searching for a value in the top row of the table and returning the value in the same column based on the index_number.

Syntax

The syntax for the HLOOKUP function in Microsoft Excel is:

HLOOKUP( value, table, index_number, [not_exact_match] )

Parameters or Arguments

value

The value to search for in the first row of the table.

table

Two or more rows of data that is sorted in ascending order.

index_number

The row number in table from which the matching value must be returned. The first row is 1.

not_exact_match

Optional. It determines if you are looking for an exact match based on value. Enter FALSE to find an exact match. Enter TRUE to find an approximate match, which means that if an exact match if not found, then it will look for the next largest value that is less than value. If this parameter is omitted, it will return an approximate match.

Note:

• 
  If index_number is less than 1, the HLOOKUP function will return #VALUE!.
  
• 
  If index_number is greater than the number of columns in table, the HLOOKUP function will return #REF!.
  
• 
  If you enter FALSE for the not_exact_match parameter and no exact match is found, then the HLOOKUP function will return #N/A.
  
• 
  See also the VLOOKUP function to perform a vertical lookup.
  

Applies To

The HLOOKUP function can be used in the following versions of Microsoft Excel:

• 
  Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000
  

Type of Excel Function

The HLOOKUP function can be used in Microsoft Excel as the following type of function:

• 
  Worksheet function (WS)
  

Example (as Worksheet Function)

Let's look at some Excel HLOOKUP function examples and explore how to use the HLOOKUP function as a worksheet function in Microsoft Excel:

Based on the spreadsheet above, the following Excel HLOOKUP function will return the following:

=HLOOKUP(10251, A1:G3, 2, FALSE)

Result: $16.80

This HLOOKUP example would return the value of $16.80. Let's take a closer look why.

First Parameter

The first parameter in the HLOOKUP function is the value to search for. So in this example, the HLOOKUP is searching for the value of 10251.

Second Parameter

The second parameter in the HLOOKUP function is the table which is set to the range of A1:G3. The HLOOKUP uses the first row in this range (ie: A1:G1) to search for the value of 10251.

Third Parameter

The third parameter is the index_number which is set to 2. This means that the second row in the table is where we will find the value to return. Since the table is set to A1:G3, the corresponding return value will be in A2:G2 (ie: second row as specified by the index_number of 2).

Fourth Parameter

Finally and most importantly is the fourth or last parameter in the HLOOKUP. In our example, it is set to FALSE. This means that you need to find an EXACT match for the value of 10251. We do not want to find a "close" match, but an EXACT match!! So if 10251 is not found in the range of A1:G1, then the HLOOKUP function should return #N/A.

Since the HLOOKUP is able to find the value of 10251 in the range A1:G1, it returns the corresponding value from A2:G2 which is $16.80.

Importance of Fourth Parameter

Let's further explore the importance of specifying TRUE vs FALSE for the last parameter in the HLOOKUP function.

So say we are looking for the Order ID of 10248, but as you can see, it is not in the range of A1:G1 in the spreadsheet above. Let's write our HLOOKUP formula with FALSE as the final parameter and another HLOOKUP formula with TRUE as the final parameter and see what happens.

=HLOOKUP(10248, A1:G3, 2, FALSE)

Result: #N/A

=HLOOKUP(10248, A1:G3, 2, TRUE)

Result: $14.00

The first HLOOKUP formula has FALSE specified as the final parameter. This means that the HLOOKUP is looking for an exact match for 10248. Since the value 10248 does not exist in the range A1:G1, the HLOOKUP function returns #N/A.

The second HLOOKUP formula has TRUE specified as the final parameter. This means that if an exact match if not found, the HLOOKUP function will look for the next largest value that is less than 10248. Now what does this mean to us?

First of all, it means that the data in A1:G3 MUST BE SORTED IN ASCENDING ORDER because the HLOOKUP is going to return the next largest value for 10248 and then stop searching. So if your data is not sorted in ascending order, you are going to get some really strange results.

Secondly, it means that the HLOOKUP function will find order 10247 as the approximate match. And therefore, return $14.00 as the result (the corresponding value from A2:G2).

Table in another sheet

Quite often we are asked the question, "What is an example of a HLOOKUP when the table is on another sheet?"

To answer that question, let's modify our example above and assume that the table is on Sheet2 in the range A1:G3.

We could rewrite our example as follows:

=HLOOKUP(10251, Sheet2!A1:G3, 2, FALSE)

By preceding the table range with the sheet name and an exclamation mark, we can update our HLOOKUP to reference a table on another sheet.

Table in another sheet with spaces in sheet name

Let's throw in one more complication, what happens if your Sheet name contains spaces, then you will need to change the formula further. So let's take a look at this case...

Let's assume that the table is on a Sheet called "Test Sheet" in the range A1:G3, we would need to modify our formula as follows:

=HLOOKUP(10251, 'Test Sheet'!A1:G3, 2, FALSE)

By placing the sheet name within single quotes, we can accommodate a sheet name with spaces in our HLOOKUP function.

Absolute Referencing

Now it is important for us to mention one more mistake that is commonly made. When people use the HLOOKUP function, they commonly use relative referencing for the table range like we did in 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. Let's explain further...

So if you had the following formula in cell J1:

=HLOOKUP(10251, A1:G3, 2, FALSE)

And then you copied this formula from cell J1 to cell K2, it would modify the HLOOKUP formula to this:

=HLOOKUP(10251, B2:H4, 2, FALSE)

Since your table is found in the range A1:G3 and not B2:H4, your formula would return erroneous results in cell K2. To ensure that your range is not changed, try referencing your table range using absolute referencing as follows:

=HLOOKUP(10251, $A$1:$G$3, 2, FALSE)

Now if you copy this formula to another cell, your table range will remain $A$1:$G$3.

How to Handle #N/A Errors

Finally, let's look at how to handle instances where the HLOOKUP function does not find a match and returns the #N/A error. In most cases, you don't want to see #N/A but would rather display a more user-friendly result.

For example, if you had the following formula:

=HLOOKUP(10251, $A$1:$G$3, 2, FALSE)

Instead of displaying #N/A error if you do not find a match, you could return the value "Not Found". To do this, you could modify your HLOOKUP formula as follows:

=IF(ISNA(HLOOKUP(10251, $A$1:$G$3, 2, FALSE)), "Not Found", HLOOKUP(10251, $A$1:$G$3, 2, FALSE))

This new formula will use the ISNA function to test if the HLOOKUP returns a #N/A error. If the HLOOKUP returns #N/A, then the formula will output "Not Found". Otherwise, it will perform the HLOOKUP as before.

This is a great way to spruce up your spreadsheet so that you don't see traditional Excel errors.