What are the Logical and Reference functions in Excel ?

1. AND
Formula:
=AND(Condition 1, Condition 2, ...)

Returns TRUE if all the conditions are met, ie.e., evaluated to TRUE; returns FALSE if one or more conditions are not met, i.e., evaluated to FALSE.

One common use for the AND function is to expand the usefulness of other functions that perform logical tests. For example, the IF function performs a logical test and then returns one value if the test evaluates to TRUE and another value if the test evaluates to FALSE. By using the AND function as the logical_test argument of the IF function, you can test many different conditions instead of just one.

Note:
The data must evaluate to logical values, such as TRUE or FALSE, or the conditions must be arrays or references that contain logical values.
If an array or reference data contains text or empty cells, those values are ignored.
If the specified range contains no logical values, the AND function returns the #VALUE! error value.
Example:
A1 = 90

The formula =AND( 9<A1, A1<99) gives us the result as TRUE
The formula =AND( 9>A1, A1<99) will give us the result as FALSE, as the first condition is not met i.e., 9 is not greated than 90.


2. IF
Formula:
=IF(logical test, value if logical test true, value if if logical test false)

The IF function returns one value if a condition you specify is met i.e., evaluated to TRUE, and another value if that condition evaluates to FALSE.

Example:
A1 = 21
The formula =IF(A1>20,"Over 20","20 or less") returns "Over 20" if A1 is greater than 20, and "20 or less" if A1 is less than or equal to 20.

Note:
Up to 64 IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. If any of the arguments to IF are arrays, every element of the array is evaluated when the IF statement is carried out. Excel provides additional functions that can be used to analyze your data based on a condition. For example, to count the number of occurrences of a string of text or a number within a range of cells, use the COUNTIF or the COUNTIFS worksheet functions. To calculate a sum based on a string of text or a number within a range, use the SUMIF or the SUMIFS worksheet functions.


3. IFERROR
Formula:
=IFERROR(value, value if error)

This function returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula. Use the IFERROR function to trap and handle errors in a formula.

Note:
The value to return if the formula evaluates to an error. The following error types are evaluated: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.If value or value_if_error is an empty cell, IFERROR treats it as an empty string value ("").If value is an array formula, IFERROR returns an array of results for each cell in the range specified in value.

Example:
A1 = 10, B1 = 5
A2 = 10, B2 = 0

The formula =IFERROR(A1/B1,"Check the numbers in calculation") will return output as 2, as there is no error in the calculation.
The formula =IFERROR(A2/B2,"Check the numbers in calculation") will return output as "Check the numbers in calculation", as 10/0 will retrun us with #DIV/0! error.

4. OR
Formula:
=OR(Conditon 1, Conditon 2, ...)

This formula returns TRUE if any of the conditions are TRUE; returns FALSE if all the conditions are FALSE.

The conditions must evaluate to logical values such as TRUE or FALSE, or in arrays or references that contain logical values.
If an array or reference argument contains text or empty cells, those values are ignored.
If the specified range contains no logical values, OR returns the #VALUE! error value.
You can use an OR array formula to see if a value occurs in an array. To enter an array formula, press CTRL+SHIFT+ENTER.
Example:
The formula =OR(1+1=2, 2+2=4) will give us output as TRUE
The formula =OR(1+1=2, 2+2=2) will give us output as TRUE
The formula =OR(1+1=1, 2+2=2) will give us output as FALSE

in first example both the conditions are true, hence the output is TRUE.
in second exmaple first condition is true, hence the output is TRUE.
in third example both the conditions are false, hence the output is FALSE.


5. CHOOSE
Formula:
=CHOOSE(index number, value1, value2, ...)

This function uses the index number to return a value from the list of value arguments. Use CHOOSE to select one of up to 254 values based on the index number.

Example:
If value1 through value7 are the days of the week, CHOOSE returns one of the days when a number between 1 and 7 is used as Index Number.

A1 = 21-Nov-2014, A2 = Weekday(A1) output in A2 = 6

=CHOOSE(A2,"Sun","Mon","Tue","Wed","Thu","Fri","Sat") will return as output as Fri, as Fri is the the sixth value in the CHOOSE function.

we can further merge the weekday formula to work with CHOOSE:
=CHOOSE(Weekday(A1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")

Note:
If index_num is an array, every value is evaluated when CHOOSE is evaluated.
The value arguments to CHOOSE can be range references as well as single values.


6. COLUMN
Formula:
=COLUMN(reference)

This formula returns the column number of the given cell reference. For example, the formula =COLUMN(E9) returns 5, because column E is the fifth column.

Note:
If the reference data is a range of cells, and if the COLUMN function is not entered as a horizontal array formula, the COLUMN function returns the number of the leftmost column.
If the reference data is omitted, it is assumed to be the reference of the cell in which the COLUMN function appears.
The reference data cannot refer to multiple areas.


7. HLOOKUP
Formula:
=HLOOKUP(lookup value, data range, row index number, range lookup)

The Hlookup function searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array.

We can Use HLOOKUP when our comparison values are located in a row across the top of a table of data, and we want to look down a specified number of rows. The H in HLOOKUP stands for "Horizontal."

Note:
If HLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than lookup_value.
If lookup_value is smaller than the smallest value in the first row of table_array, HLOOKUP returns the #N/A error value.
If range_lookup is FALSE and lookup_value is text, you can use the wildcard characters, question mark (?) and asterisk (*), in lookup_value. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
For example of HLOOKUP click here.


8. INDEX
Formula:
=INDEX(data range, row number, column number)

The INDEX formula returns a value or the reference to a value from within a table or range. The INDEX function returns the value of an element in a table or an array , selected by the row and column number indexes.

Note:
If both the row number and column number values are used, the INDEX returns the value in the cell at the intersection of row number and column number.
If you set row number or column number to 0 (zero), the INDEX returns the array of values for the entire column or row, respectively. To use values returned as an array, enter the INDEX function as an array formula in a horizontal range of cells for a row, and in a vertical range of cells for a column. To enter an array formula, press CTRL+SHIFT+ENTER.
Row number and column number must point to a cell within array; otherwise, INDEX will return the #REF! error value.
Example:
A1 = Orange, B1 = Grapes, A2 = Watermelon and B2 = Pears

The formula =INDEX(A1:B2,2,2) will give us the output as Pears, as the pears is located in the 2nd row and 2nd column of the array.

Similarly the formula =INDEX(A1:B2,2,1) will return the output as Watermelon.


9. INDIRECT
Formula:
=INDIRECT(reference text, range)

This function returns the reference specified by a text string. References are immediately evaluated to display their contents. We can use INDIRECT when we need to change the reference to a cell within a formula without changing the formula itself.

Note:
If the referenc text is not a valid cell reference, INDIRECT returns the #REF! error value.
If the reference text refers to a cell range outside the row limit of 1,048,576 or the column limit of 16,384 (XFD), INDIRECT returns a #REF! error.
This behavior is different from Excel versions earlier than Microsoft Office Excel 2007, which ignore the exceeded limit and return a value.

Example:
A1 = Sanjay, B1 = 100,
A2 = Kiran, B2 = 200,
A3 = Vijay, B3 = 150

The formula =INDIRECT($A$2) will give us output as 200, as this is the value of our reference Kiran in cell A2.
Similarly the formula =INDIRECT($A$3) will give us output as 150


10. MATCH
Formula:
=MATCH(value we need to lookup, lookup data range, match type)

The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 9, 99, and 39, then the formula
=MATCH(99,A1:A3,0) will give us the output as the number 2, because 99 is the second item in the range.

MATCH is a very useful function to locate the position of the data. MATCH can be merged with the INDEX function to find the row and column index number.

Note:
MATCH returns the position of the matched value within lookup array, not the value itself. For example, MATCH("b",{"a","b","c"},0) returns 2, which is the relative position of "b" within the array {"a","b","c"}.
MATCH does not distinguish between uppercase and lowercase letters when matching text values.
If MATCH is unsuccessful in finding a match, it returns the #N/A error value.
If match type is 0 and lookup value is a text string, you can use the wildcard
characters — the question mark (?) and asterisk (*) — in the lookup value argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

Example:
A1 = Oranges,        B1 = 9,
A2 = Grapes,          B2 = 19,
A3 = Pears,            B3 = 29,
A4 = Watermelon,   B4 = 39

The formula =MATCH(39, B1:B4,0) will give us the output as 4, as it is the fourth positon of 39 in our array. (0 is for exact match)

Similarly the formula =MATCH(30, B1:B4,1) will give us the output as 3, as the lowest number close to 30 is 29 and the position of 29 is the third positon in our array. (1 is for approximate match)