Excel – Advance Excel

Functions and Brief Explanation

• VLOOKUP – Getting the value from the data range using the column index number)

• HLOOPKUP- Getting the value from the data range using the row index number)

• IF –  Logical test , conditional  meets or does not meet. There will be nested cases

• SUMIF – Getting the total sum value within the data range if the one condition is matched.

• SUMIFS – Getting the total sum value if the multiple conditions are matched.

• COUNTIF – Get the no of counts if the condition meets

• COUNTIFS –  Get the no of counts from the multiple data ranges if the conditions meet

• CONCAT – Combine the cells into one cell

Most common used advanced Excel Functions

• ´VLOOKUP – Getting the value from the data range using the column index number)
  • ´VLOOKUP( lookup_value, table_array, col_index_number, range_lookup/logical )
    • ´logical value : 0 = exact match, 1: omitted/closer match.
• ´HLOOPKUP – Getting the value from the data range using the row index number)

• ´If – Logical test , conditional  meets or does not meet. There will be nested cases
• ´SUMIF – Getting the total sum value within the data range if the one condition is matched.
• ´SUMIFS – Getting the total sum value if the multiple conditions are matched.

• ´COUNTIF – Get the no of counts if the single condition meets
  • ´ COUNTIF(range, Criteria)
• ´COUNTIFS – Get the no of counts from the multiple data ranges if the conditions meet
  • ´COUNTIFS(Criteria_range1, Criteria1, Criteria_range2, Criteria2, …)

• ´CONCATENATE / CONCAT – Combine the cells into one cell
  • ´CONCATENATE( text1, text2, text3, text4 )
  • ´Eg: CONCATENATE(employees!B2,” “, employees!C2)

“IF ” statement