Most Popular Excel Formulas and How to Use Them

8] =HLOOKUP

=HLOOKUP is a function used to look up values in a table or range of cells based on specific criteria, and return the corresponding value from the specified row. This function belongs to the Search and Reference category, and is a function similar to the VLOOKUP function, but different in terms of data orientation. The HLOOKUP function looks up the value in the first row of the table or range of cells, and returns the value from the specified row, while the VLOOKUP function looks up the value in the first column of the table or range of cells, and returns the value from the specified column.

The syntax of the =HLOOKUP function is as follows:

=HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])

Where:

• lookup_value is the value that you want to look up in a table or range of cells. This value can be a number, text, date, time, or cell reference.
• table_array is the table or range of cells that contains the data you want to search. This table or range of cells must have at least two rows, and the value you want to look up must be in the first row.
• row_index_num is the row number in a table or range of cells that contains the values that you want to return. This line number must be a positive number, starting at 1 for the first row, 2 for the second line, and so on.
• [range_lookup] is an optional argument that specifies whether you want to look up an exact value or one that is approximate. This value can be TRUE or FALSE, or 1 or 0. If you enter TRUE or 1, or leave these arguments blank, the =HLOOKUP function looks for values that come close to, that is, values less than or equal to lookup_value, assuming that the first row in the table or range of cells is already sorted ascendingly. If you enter FALSE or 0, then the =HLOOKUP function looks up the exact value, which is the exact same value as lookup_value, regardless of the order of the first row in the table or range of cells.

Here is an example of using the =HLOOKUP function:

•  =HLOOKUP(“Value “,A1:D5,2,FALSE) returns the value 80, which is the value returned from the second row in the table or cell range A1:D5, based on the “Value”value searched in the first row, by using the exact value lookup.
• =HLOOKUP(“Information “,A1:D5,3,TRUE) returns the text“Pass”, which is the value returned from the third row in the table or cell range A1:D5, based on the “Description”value searched in the first row, by using an approximate value lookup.
• =HLOOKUP(B1,A1:D5,4,FALSE) returns the text“A001″, which is the value returned from the fourth row in the table or cell range A1:D5, based on the value that is in cell B1 that was searched in the first row, by using the exact value lookup.
• =HLOOKUP(45,A1:D5,1,TRUE) returns the text “Value”, which is the value returned from the first row in the table or cell range A1:D5, based on the value 45 searched in the first row, by using an approximate value lookup.

9] =INDEX

=INDEX is a function used to return the value of a specific cell in a table or range of cells, based on the specified row and column numbers. This function belongs to the Lookup and Reference category, and is one of the useful functions for retrieving data from different sources or for generating dynamic cell references.

The syntax of the =INDEX function is as follows:

=INDEX(array,row_num,[column_num])

Where:

• an array is a table or range of cells that contains the data you want to return. This table or range of cells must have at least one row and one column.
• row_num is the row number in a table or range of cells that contains the value you want to return. This line number must be a positive number, starting at 1 for the first row, 2 for the second line, and so on.
• [column_num] is the column number in the table or range of cells that contains the value you want to return. This column number must be a positive number, starting at 1 for the first column, 2 for the second column, and so on. If you leave this argument blank, the =INDEX function returns the entire row specified by the row_num.

Here is an example of using the =INDEX function:

•  =INDEX(A1:D5,2,3) returns the text “Pass”, which is the returned value from cells that are in the second row and third column in the table or cell range A1:D5.
• =INDEX(A1:D5,4,0) returns cell range D4:D5, which is the entire row returned from the table or cell range A1:D5, based on the specified row number 4.
• =INDEX(A1:D5,0,4) returns cell range D1:D5, which is the entire column returned from the table or cell range A1:D5, based on the specified column number 4.
• =INDEX(A1:D5,5,2) returns the value 40, which is the returned value from cells that are in the fifth row and second column in the table or cell range A1:D5.