100 Nos Of Formula and Functions
In MSExcel
Part 6
91-100: Lookup and Reference Functions
91. VLOOKUP - Searches for a value
in the first column of a range and returns a value in the same row from another
column.
o Example: =VLOOKUP(A1,
B2:D10, 3, FALSE)
92.HLOOKUP - Searches for a value in
the first row of a range and returns a value in the same column from another
row.
o Example: =HLOOKUP(A1,
B1:H10, 4, FALSE)
93.INDEX - Returns a value from within
a specified range based on row and column numbers.
o Example: =INDEX(A1:C10, 2,
3)
94.MATCH - Returns the position of a
value in a range.
o Example:
=MATCH("Apple", A1:A10, 0)
95.LOOKUP - Finds a value in a range
and returns a corresponding value.
o Example: =LOOKUP(A1,
B1:B10, C1:C10)
96.CHOOSE - Returns a value from a list
based on a specified position.
o Example: =CHOOSE(2,
"Apple", "Banana", "Cherry")
97.OFFSET - Returns a reference to a
range that is a specified number of rows and columns from a cell.
o Example: =OFFSET(A1, 1, 2)
98.INDIRECT - Returns the reference
specified by a text string.
o Example:
=INDIRECT("A" & B1)
99.TRANSPOSE - Converts a vertical
range to a horizontal range and vice versa.
o Example:
=TRANSPOSE(A1:A10)
100.FORMULATEXT - Returns
the formula in a given cell as text. - Example: =FORMULATEXT(A1)
