LOOKUP AND REFERENCE FUNCTIONS
1. ADDRESS:- In Microsoft Excel “ADDRESS” function returns the address
on a given row and column number for a cell.
Syntax of ADDRESS function:-
=ADDRESS(row_num,column_num,[abs_num],[a1],[sheet_text])
Arguments:-
row_num:- This row number is used in cell
address.
column_num:- This column number is used in cell
address.
abs_num:- It is divided into two parts i.e. Absolute and Relative. If omitted default is Absolute and it is an optional part.
a1:- It is a reference style i.e A1(TRUE) and R1C1(FALSE). If omitted default is A1 style and it is an optional part.
sheet_text:- The name of the worksheet to use, if
omitted it takes default to current sheet and it is an optional part.
Example of ADDRESS function:-
The
following pictures show the example of the
ADDRESS function.
2. AREAS:- In Microsoft Excel “AREAS” function returns the number of
areas in a reference. An area is a range of cells or a single cell.
Syntax of AREAS function:-
=AREAS(reference)
Argument:-
reference:- A cell or a range of cell reference.
Example of AREAS function:-
The
following pictures show the example of the
AREAS function.
3. CHOOSE:- In Microsoft Excel “CHOOSE” function chooses a value or
action to perform from a list of values, based on an index number.
Syntax of CHOOSE function:-
=CHOOSE(index_num,value1,[value2],….)
Arguments:-
index_num:- The number between 1 and 254 to
choose.
value1:- The first value from which to choose.
value2:- The second value from which to choose. But it is an optional part.
Example of CHOOSE function:-
The
following pictures show the example of the
CHOOSE function.
4. COLUMN:- In Microsoft Excel “COLUMN” function finds and provides the
column number of a given cell reference.
Syntax of COLUMN function:-
=COLUMN([reference])
Argument:-
reference:- Cell reference.
Example of COLUMN function:-
The
following pictures show the example of the
COLUMN function.
5. COLUMNS:- In Microsoft Excel “COLUMNS” function finds and provides the total count of columns
in a given reference.
Syntax of COLUMNS function:-
=COLUMNS(array)
Argument:-
array:- Range of cells reference.
Example of COLUMNS function:-
The
following pictures show the example of the
COLUMNS function.
6. GETPIVOTDATA:- In Microsoft Excel “GETPIVOTDATA” function extracts data
stored in a Pivot table.
Syntax of GETPIVOTDATA function
=GETPIVOTDATA(data_field,pivot_table,[field1],[item1],[field2],[item2],….)
Arguments:-
data_field:- The Pivot table data field which you
want to retrieve from the original data field.
pivot_table:- A cell or a range of cell reference
to be searched within the Pivot table.
field1, item1, field2, item2,
etc.:- A field/item pair.
Example of GETPIVOTDATA function:-
The following pictures show the example of the GETPIVOTDATA function.
7. HLOOKUP:- In Microsoft Excel “HLOOKUP” function is a horizontal lookup by finding for a value in
the top row of the table and returning the value in the same column on the
basis of index_num.
Syntax of HLOOKUP function:-
=HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])
Arguments:-
lookup_value:- The value which is search in the
first row of the table.
table_array:- Two or more rows of data is sorted in
order (ascending).
row_index_num:- The index number from which the
matching value must be returned.
range_lookup:- It is an optional part, if you enter FALSE then HLOOKUP find an exact match
and if TRUE then it find approximate
match. If it is omitted then TRUE is
the default.
Example of HLOOKUP function:-
Through HLOOKUP function you can find the
Rate/Kg and Quantity/Kg for individual Fruit.
The
following pictures show the example of the
HLOOKUP function.
8. HYPERLINK:- In Microsoft Excel “HYPERLINK” function returns a
hyperlink from a given destination. Through the hyperlink function you can
build links to files, a page of any website, to workbook, etc.
Syntax of HYPERLINK function:-
=HYPERLINK(link_location,[friendly_name])
Arguments:-
link_location:- The location to the file or page to
be opened.
friendly_name:- It is a link text to highlight in a
cell and it is an optional part.
Example of HYPERLINK function:-
The
following pictures show the example of the
HYPERLINK function.
9. INDEX:- In Microsoft Excel “INDEX” function returns the value in a
range or array at a given position. Through the index function you can retrieve
a single value or entire rows and columns.
Syntax of INDEX function:-
=INDEX(array,row_num,[column_num])
Arguments:-
array:- A range of cells or an array.
row_num:- The position of the row in the
reference of array.
column_num:- The position of the column in the
reference of array and it is an optional part.
Example of INDEX function:-
The
following pictures show the example of the
INDEX function.
10. INDIRECT:- In Microsoft Excel “INDIRECT” function returns the
reference specified by a text string. You can use the function to create a
reference that would not change, if you insert rows and columns in the
worksheet.
Syntax of INDIRECT function:-
=INDIRECT(ref_text,[a1])
Argument:-
ref_text:- Cell reference or a range of cells.
a1:- It is an optional part. It has two
styles i.e. A1 (TRUE) and R1C1 (FALSE). If it omitted then
default is A1 (TRUE).
Example of INDIRECT function:-
The
following pictures show the example of the
INDIRECT function.
The INDIRECT
function locks a specific cell in a formula. If you inserted rows and columns
then the reference does not change. Without INDIRECT function, the reference automatically adjusts.
To see how
it works, then follow the below example in pictorial format.
a) In cell B3:B8 enter a list of number.
b) Copy the
list into C3:C8 cell.
c) In cell B9 type SUM formula =SUM(B3:B8).
d) In cell C9 type this formula =SUM(INDIRECT(“C3”):C8).
Insert a
row, now you can see the difference between the formulas after inserted a blank
row above row 3 and input 200 for SUNDAY
in cell B3 and C3.
The total
amount changes in column C because
the start cell locked at C3 through
the INDIRECT function.
The total
amount does not change in column B
because the start cell shifts down to B4.
11. LOOKUP:- In Microsoft Excel “LOOKUP” function performs an
approximate match lookup in a one column or one row range and return the value
from another one column or one row range. Lookup make it useful for serving
specific problem in Excel.
Syntax of LOOKUP function:-
=LOOKUP(lookup_value,lookup_vector,[result_vector])
Arguments:-
lookup_value:- The value to search for.
lookup_vector:- One row or one column range to
search.
result_vector:- One row or one column range of
results. It is an optional part.
Example of LOOKUP function:-
The
following pictures show the example of the
LOOKUP function.
OR
Syntax of LOOKUP function:-
=LOOKUP(lookup_value,array)
Arguments:-
lookup_value:- The value to search for.
array:- Two dimensional array of data,
containing values to find in the first row or column and returned values in the
last row or column.
Example of LOOKUP function:-
The
following pictures show the example of the
LOOKUP function.
12. MATCH:- In Microsoft Excel “MATCH” function find for item in a
range of cells and returns the appropriate position of that item in the range.
Syntax of MATCH function:-
=MATCH(lookup_value,lookup_array,[match_type])
Arguments:-
lookup_value:- The value to match in lookup_array.
lookup_array:- A range of cells or an array
reference.
match_type:- There are three types of match are
available i.e. 1=exact or next
smallest, 0=exact match, -1=exact or next largest. It is an
optional part, if omitted then the default match_type is 1.
Example of MATCH function:-
The
following pictures show the example of the
MATCH function.
13.OFFSET:- In Microsoft Excel “OFFSET” function returns a range i.e.
a specified number of rows and columns from a referential or cell range. The OFFSET function returns result can be a
single cell or a range of multiple cells.
Syntax of OFFSET function:-
=OFFSET(reference,rows,cols,[height],[width])
Arguments:-
reference:- The starting point of a cell or cell range.
rows:- The number of rows to OFFSET below the starting reference.
cols:- The number of columns to OFFSET to the right of the starting
reference.
height:- The returned reference height in
rows. It is an optional part.
width:- The returned reference width in
columns. It is an optional part.
Example of OFFSET function:-
The
following pictures show the example of the
OFFSET function.
14. ROW:- In Microsoft Excel “ROW” function returns the row number
from a given reference. E.g. if you enter =ROW(F10)
then it will returns the value of row is 10. So it is the 10th row
of the spreadsheet. If you are not given any reference in ROW function then it
will return the row number of the cell which contains the formula.
Syntax of ROW function:-
=ROW([reference])
Argument:-
reference:- A reference to a cell or a range of
cells.
Example of ROW function:-
The
following pictures show the example of the
ROW function.
15. ROWS:- In Microsoft Excel “ROWS” function count the number of
rows in the given reference. E.g. if you enter =ROWS(B1:B10), it will return
the result value 10.
Syntax of ROWS function:-
=ROWS(array)
Argument:-
array:- A reference to a range of cells.
Example of ROWS function:-
The
following pictures show the example of the
ROWS function.
16. TRANSPOSE:- In Microsoft Excel “TRANSPOSE” function converts a
vertical range to a horizontal range or a horizontal range to vertical range.
Syntax of TRANSPOSE function:-
=TRANSPOSE(array)
Argument:-
array:- The range of cell to transpose.
Example of TRANSPOSE function:-
The
following pictures show the example of the
TRANSPOSE function.
Step1:- Make a horizontal table A2:B6 for Transpose same as below.
Step2:- Select cell D2:H3 then type the formula in D2
cell. The formula is “=TRANSPOSE(A2:B6)”
after writing don’t press Enter key directly, press “Ctrl + Shift + Enter” keys.
17. VLOOKUP:- In Microsoft Excel “VLOOKUP” function executes a vertical
lookup by searching the first column value of a table and the value is
returning in the same row in the position on the index number.
Syntax of VLOOKUP function:-
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
Arguments:-
lookup_value:- The value to search for the first
column of the table.
table_array:- The table from which to pick up a
value.
col_index_num:- The column in the table from which to
pick up a value.
range_lookup:- TRUE = approximate match and FALSE = exact match. It is an optional part and if omitted the default option is TRUE.
Example of VLOOKUP function:-
The following pictures show the example of the VLOOKUP function.
👍🏻👍🏻bro
ReplyDelete💙Frm omm