# Excel Functions

## Filter

The (*) operator returns ANDfunction result

=FILTER(A5:D20,(C5:C20=H1)*(A5:A20=H2),"")

Above means get a sub list of A5:D20 which has C5:C20=H1 and A5:A20=H2. The H1 and H2 are values to be matched in column C and column A.

## IS functions

Function Returns TRUE if -

• ISBLANK Value refers to an empty cell.
• ISERR Value refers to any error value except #N/A.
• ISERROR Value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).
• ISLOGICAL Value refers to a logical value.
• ISNA Value refers to the #N/A (value not available) error value.
• ISNONTEXT Value refers to any item that is not text. (Note that this function returns TRUE if value refers to a blank cell.)
• ISNUMBER Value refers to a number.
• ISREF Value refers to a reference.
• ISTEXT Value refers to text.

## VLOOKUP

Return value searching

=VLOOKUP(value, table_array, col_index,[range_lookup])

## MATCH

Return index of a value in an range.

=MATCH(25,A1:A3,0)

## INDEX

Return value of specific cell in an range

=INDEX(A2:B3,2,2)

## VLOOKUP vs INDEX MATCH

=INDEX(range, MATCH(lookup_value, lookup_range, match_type))

Better than VLOOKUP as

• Dynamic Column Reference
• Insert/Delete Columns Safely
• Lookup Value Size Limit
• Higher processing speed
• Lookup Value Position

## COUNTIF vs COUNTIFS function

=COUNTIF(A2:A5,A4)
=COUNTIFS(B2:B5,"=Yes",C2:C5,"=Yes")
=COUNTIFS(A2:A7, "<" & A6,B2:B7,"<" & B4)