Category: excel

Excel Functions

Posted on by Bian Xi Leave a Comment on Excel Functions

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)

References

FILTER function
MATCH function
INDEX function
Difference between ISNA and ISERROR
COUNTIF function