To retrieve the last value, we use a combination of “INDEX”, “MAX”, and “COLUMN” functions, and to retrieve the last character in a row, we use the “HLOOKUP” and “REPT” functions in Microsoft Excel 2010.
INDEX: The Index formula returns a value from the intersection between the row number and the column number in an Array. There are 2 syntaxes for the “INDEX” function.
1st Syntax of “INDEX” function:=INDEX (array, row_num, [column_num])
2nd Syntax of “INDEX” function:=INDEX (reference, row_num, [column_num], [area_num])
For example:- We have data in range A1:C5 in which column A contains Order Id, column B contains Unit Price, and column C contains Quantity.
Follow below given steps:-
MAX: This function is used to return the largest number in a set of values. It ignores logical values and text.
Syntax of “MAX” function: =MAX(number1, [number2],….)
Example: Range A1:A4 contains numbers, and we need to return the maximum number from this range.
Follow the below given steps:-
COLUMN: This function is used to return the column number of a reference.
Syntax of “COLUMN” function: =COLUMN(reference)
For Example: If we want to return the column number for the cell A1, we need to follow below given steps:-
HLOOKUP: The function is used for looking a value in the top row of a table or array of values and returns the value in the same column from a row you specify.
Syntax of “HLOOKUP” function: =HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])
Uses of Hlookup Function
We use this function to pick the values from one data to another, but to meet the condition we should have the common values in both data sets so that we can easily pick the value.
Let’s take an example and understand how and where we can use Vlookup function in Microsoft Excel 2010.
Example 1: We have 2 data sets in which we require joining date from 1st data to 2nd data.
To pick the value from 1st data to 2nd data, the common value we have is Emp. Code. So we will put the Hlookup function through Emp. Code. We need to follow below mentioned steps to pick the joining date:-
Note: If the common value we do not have in other data from which we want to pick the data, then the formula will give the #N/A error. As you can see in above snapshot, the employee code L1089 in not available in 1st data set so formula is giving the error.
REPT:- This function is used to repeat text a given number of times. Use REPT to fill a cell with a number of instances of text string.
Syntax of “REPT” function: =COLUMN(reference)
For example:- Write the formula in cell A1 =REPT("a",10), and press Enter on your keyboard. The function will show that “a” is repeating 10 times.
MODE: This function is available for compatibility with Excel 2007 and earlier. It returns the most frequently occurring or repetitive value in an array or range of data.
Syntax of “MODE” function: =MODE(number1, [number2],……..)
For Example:-We have number list in the range A1:A5.
To return the mode of numbers, follow below given steps:-
IF: - Check whether a condition is met and returns one value if True and another value if False.
The syntax of “IF” function =if(logical test,[value_if_true],[value_if_false])
Firstly, the formula will do the logical test to know if the output of logical test is true or false.
For example:Cell A2 and A3 contain the number 3 and 5. If the number is 3, the formula should display “Yes”, else “No”.
Let’s take an example to understand how we can retrieve the last value and the last character in a row.
We have data in range A2:E5 in which cells contain characters and numbers individually. Some of the cells in the range may be empty.
We want to retrieve both the last values (of any type) and the last character that appears in each row of the range.
To find the last character for each row, follow below given steps:-
To find the last value (of any type) in each row, follow below given steps:-
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.