Skip to content

Logic, Lookups & Rounding

Deep Structural Lookups & Nested Architectures

Lookup Models

VLOOKUP

Looks up a value in the first column of a table and returns a value in the same row from a specified column.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

  • Wildcard Variation: =VLOOKUP(cell_value & "*", $H$4:$I$8, 2, FALSE)

  • Rule: Always sort the lookup array in ascending order if you are using approximate matches to prevent errors

XLOOKUP

A modern, flexible lookup function that searches arrays in any direction (vertically, horizontally, or diagonally) and doesn't require sorted data.

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
  • Wildcard Variation: =XLOOKUP("" & cell_range & "", lookup_array, return_array, "Not Found", 2)

INDEX & MATCH Matrix

Combines two functions to look up values anywhere in a table, bypassing the limitations of VLOOKUP.

  • INDEX extracts values based on row/column numbers: =INDEX(array, row_num, [col_num])
  • MATCH finds the exact position number of a value: =MATCH(lookup_value, lookup_array, [match_type])
  • Combined Syntax:
=INDEX(array, MATCH(lookup_value, lookup_array, 0), col_num)

Complex Nested Logic Configurations

Multi-Tier Nested IF Statements

Evaluates multi-tiered scenarios sequentially.

  • Greater-Than Approach (Descending Sort Order):
=IF(E3>80, "NO COACHING", IF(E3>60, "RECOMMEND COACHING", "MANDATORY COACHING"))
  • Less-Than Approach (Ascending Sort Order):
=IF(E3<60, "MANDATORY COACHING", IF(E3<80, "NO COACHING", "RECOMMEND COACHING"))
  • Standard Academic Grading Matrix System:
=IF(E2>=90,"A",IF(E2>=80,"B",IF(E2>=70,"C",IF(E2>=60,"D","E"))))

Statistical Rounding, Ranks, & Frequencies

Math Rounding Controls

  • =ROUND(cell, 0) — Rounds to the nearest whole integer.
  • =ROUND(cell, 1) — Rounds to one decimal place.
  • =ROUNDUP(cell, -1) — Rounds up to the nearest multiple of 10.
  • =ROUNDDOWN(cell, -1) — Rounds down to the nearest multiple of 10.
  • =MROUND(cell, 5) — Rounds to the nearest specified multiplier (e.g., nearest 5).
  • =CEILING.MATH(cell, 50) — Rounds upward to the nearest higher multiple of 50.
  • =FLOOR.MATH(cell, 25) — Rounds downward to the nearest lower multiple of 25.

Advanced Statistical Commands

  • =LARGE(array, k) — Extracts the k-th largest value from a dataset (e.g., =LARGE(C7:C12, 2) for the 2nd highest value).
  • =SMALL(array, k) — Extracts the k-th smallest value.
  • =RANK(number, ref, [order]) — Computes the rank of a value within an array. =RANK(K5, $K$5:$K$19, 1) (where 1 ranks in ascending order).
  • =PERCENTILE.INC(array, k) — Finds the k-th percentile of values in a range, where k is a decimal between 0 and 1. (e.g., =PERCENTILE.INC($F$4:$F$18, 0.90) for the 90th percentile).
  • =FREQUENCY(data_array, bins_array) — Calculates how often values occur within a range of intervals (bins). Entered as an array formula.