Lookup functions are the bridge between separate tables of data — they find a value in one table and return related information from another. In South African offices, VLOOKUP is used daily for payroll tax tables, product pricing, employee records, customer accounts, and commission schedules. This module covers VLOOKUP in full depth, its common errors and limitations, and the modern replacements: HLOOKUP for horizontal lookups, INDEX+MATCH for flexible two-way lookups, and XLOOKUP (Excel 365) which solves every VLOOKUP limitation. Rounding is also covered fully here as it is inseparable from financial lookups and payroll calculations.
Rounding functions control the precision of numeric results. Formatting a number to 2 decimal places only displays it rounded — the full unrounded value is still used in calculations. Rounding functions actually change the stored value.
| Function | Rule | 15.456 to 2dp | 15.454 to 2dp |
|---|---|---|---|
| ROUND | Standard: 5 rounds up | 15.46 | 15.45 |
| ROUNDUP | Always away from zero | 15.46 | 15.46 |
| ROUNDDOWN | Always toward zero (truncate) | 15.45 | 15.45 |
| CEILING | Up to nearest multiple of significance | CEILING(15.456,0.05) = 15.50 | CEILING(15.454,0.05) = 15.50 |
| FLOOR | Down to nearest multiple of significance | FLOOR(15.456,0.05) = 15.45 | FLOOR(15.454,0.05) = 15.45 |
| MROUND | Standard round to nearest multiple | MROUND(15.456,0.05) = 15.45 | MROUND(15.454,0.05) = 15.45 |
| INT | Integer — always down (negative infinity) | 15 | 15 |
| TRUNC | Integer — always toward zero | 15 | 15 |
VLOOKUP searches for a value in the leftmost column of a table and returns a value from the same row in a column you specify. The V stands for Vertical — it searches down a column.
| Argument | What It Is | Example |
|---|---|---|
| lookup_value | The value to search for — a cell reference, text in quotes, or number. Must match the leftmost column of the table. | A2 (employee ID, product code, etc.) |
| table_array | The table to search — must include the search column as the first (leftmost) column, plus all columns you might return. Use absolute references so the table doesn't shift when copied. | $F$2:$H$100 |
| col_index_num | Which column of the table to return. Column 1 = the search column, 2 = the next column to its right, etc. | 2 = return the 2nd column of the table |
| range_lookup | FALSE (or 0) = exact match only — almost always what you want. TRUE (or 1, or omitted) = approximate match — requires the table to be sorted ascending. | 0 or FALSE for exact match |
| Mode | When to Use | Table Requirement | Behaviour if Not Found |
|---|---|---|---|
| Exact Match (0 / FALSE) | Employee IDs, product codes, invoice numbers, names — any lookup where the value must match precisely | Table does not need to be sorted | Returns #N/A error |
| Approximate Match (1 / TRUE / omitted) | Tax brackets, commission tiers, salary bands — where you want the largest value ≤ the lookup value | Must be sorted ascending — incorrect results if unsorted | Returns the closest match below the lookup value |
If col_index_num is wrong, VLOOKUP returns a value from the wrong column. Always count columns from the left edge of table_array — the first column is 1 (your search column), the next is 2, etc.
If table_array is not absolute ($F$2:$H$100), copying the VLOOKUP formula down shifts the table range (F3:H101, then F4:H102…) causing #N/A or wrong results. Always use absolute references for the table.
VLOOKUP can only return values from columns to the right of the search column. If you need to return a value from a column to the left of the search column, VLOOKUP cannot do it. Use INDEX+MATCH or XLOOKUP instead.
If someone inserts or deletes a column in the lookup table, the col_index_num is now wrong (it still says "2" but the salary has moved to column 3). INDEX+MATCH avoids this by referencing columns by name, not number.
The search value must be in the leftmost column of table_array. You cannot search a table by any middle or right column. XLOOKUP and INDEX+MATCH have no such restriction.
HLOOKUP searches across the top row of a table and returns a value from the same column in a row you specify. H stands for Horizontal — it searches across a row instead of down a column.
Arguments mirror VLOOKUP: lookup_value searches the first row; row_index_num specifies which row to return (1 = header row, 2 = first data row, etc.).
HLOOKUP has the same limitations as VLOOKUP. It can only return rows below the header row, and it is being replaced by XLOOKUP in modern Excel. Use INDEX+MATCH or XLOOKUP for more flexibility.
INDEX and MATCH are two separate functions that, when combined, create a lookup formula more powerful and flexible than VLOOKUP. They are the professional standard in financial modelling and complex data work.
| Feature | VLOOKUP | INDEX + MATCH |
|---|---|---|
| Look left | ❌ Cannot return left of search column | ✓ Search column can be anywhere; return column can be anywhere |
| Column inserts | ❌ col_index_num breaks when columns are inserted | ✓ References columns by range, not number — self-correcting |
| Search column position | ❌ Must be the leftmost column in table_array | ✓ Can search any column regardless of position |
| Performance on large data | Slightly slower | Slightly faster with MATCH on large datasets |
| Two-way lookup | ❌ Not directly possible | ✓ Nest two MATCH calls for row and column lookup |
XLOOKUP is the successor to VLOOKUP and HLOOKUP. It addresses every limitation of VLOOKUP and is simpler than INDEX+MATCH. If you have Excel 365 or Excel 2021, use XLOOKUP instead of VLOOKUP.
| Argument | What It Is | Default |
|---|---|---|
| lookup_value | The value to search for | Required |
| lookup_array | The column (or row) to search in — can be anywhere in the table | Required |
| return_array | The column (or row) to return the result from — can be left, right, or multiple columns | Required |
| [if_not_found] | What to return when no match is found — replaces IFERROR wrapper | #N/A if omitted |
| [match_mode] | 0 = exact match, -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard | 0 (exact) |
| [search_mode] | 1 = first to last, -1 = last to first, 2 = binary asc, -2 = binary desc | 1 (first to last) |
| Task | XLOOKUP Formula |
|---|---|
| Find employee name by ID (exact match) | =XLOOKUP(A2,$F$2:$F$100,$G$2:$G$100,"Not found") |
| Left lookup (return column to the LEFT of search) | =XLOOKUP(B2,$B$2:$B$100,$A$2:$A$100,"Not found") |
| Return last matching entry (search from bottom) | =XLOOKUP(A2,$F$2:$F$100,$G$2:$G$100,"Not found",0,-1) |
| Commission tier lookup (approximate, next smaller) | =XLOOKUP(SalesAmt,$J$2:$J$6,$K$2:$K$6,0,-1) |
| Wildcard search (find by partial name) | =XLOOKUP("*Dlamini*",$G$2:$G$100,$F$2:$F$100,"None found",2) |
| Return multiple columns (name AND salary) | =XLOOKUP(A2,$F$2:$F$100,$G$2:$H$100,"Not found") (spills into 2 cells) |
| Situation | Best Function | Why |
|---|---|---|
| Simple lookup (search column on the left, return column to the right) — Excel 2019 or earlier | VLOOKUP | Simple syntax, widely understood, compatible with all Excel versions |
| Need to look left, or table structure may change | INDEX + MATCH | Works in all versions; search any column; immune to structural changes |
| Two-way lookup (find intersection of row and column) | INDEX + MATCH + MATCH | The only pre-365 way to do cross-table two-way lookups |
| Horizontal lookup (data in rows) | HLOOKUP or XLOOKUP | HLOOKUP for older Excel; XLOOKUP replaces it entirely |
| Excel 365 / 2021+ and maximum flexibility | XLOOKUP | Solves all VLOOKUP limitations; cleaner syntax; built-in error handling |
| Approximate match for tiers/bands (tax, commission) | VLOOKUP (TRUE) or XLOOKUP (-1) | VLOOKUP with TRUE; XLOOKUP with match_mode -1 |
| Workbook shared with users on older Excel versions | VLOOKUP or INDEX+MATCH | XLOOKUP errors in Excel 2019 and earlier; use compatible functions |
Q1: Write a VLOOKUP formula to find the salary of an employee whose ID is in cell A2. The employee table is in $F$2:$I$200, with ID in column F, Name in G, Department in H, and Salary in column I. Include error handling for IDs not in the table.
✓ =IFERROR(VLOOKUP(A2,$F$2:$I$200,4,0),0). The col_index_num is 4 because Salary is in the 4th column of the table_array (F=1, G=2, H=3, I=4). The 0 (or FALSE) specifies exact match — essential for IDs. IFERROR returns 0 if the ID is not found rather than showing #N/A. Using IFNA instead of IFERROR would also work and is more specific: =IFNA(VLOOKUP(A2,$F$2:$I$200,4,0),0).
Q2: The above VLOOKUP breaks when a new column is inserted between G and H in the employee table. How does INDEX+MATCH solve this problem? Rewrite the salary lookup using INDEX+MATCH.
✓ VLOOKUP uses a hardcoded col_index_num (4) which refers to column position, not the actual column. If a new column is inserted between H and I, Salary moves to column 5 but the formula still says 4, returning the wrong data silently. INDEX+MATCH avoids this by referencing the return column directly by its range address: =IFERROR(INDEX($I$2:$I$200,MATCH(A2,$F$2:$F$200,0)),0). MATCH finds the position of the ID in column F. INDEX uses that position to return the value from column I (Salary). If a new column is inserted between G and H, Excel automatically adjusts the $I$2:$I$200 reference to the new column position — the formula remains correct without any manual editing.
Q3: You have a commission rate table with minimum sales thresholds in column A (0; 50000; 100000; 200000; 500000) and rates in column B (5%; 7%; 10%; 13%; 15%). A salesperson's total sales are in cell D2. Write a VLOOKUP formula to find their commission rate. What requirement must the table meet?
✓ =VLOOKUP(D2,$A$2:$B$6,2,1). The last argument is 1 (or TRUE) for approximate match, which finds the largest value in column A that is less than or equal to D2. For sales of R150,000, it finds the 100,000 threshold and returns 10%. For R600,000, it finds the 500,000 threshold and returns 15%. Critical requirement: column A (the search column) must be sorted in ascending order. If the thresholds are out of order, the approximate VLOOKUP returns incorrect results without any error — this is a silent bug that is very hard to detect. Always verify the sort order when using approximate match.
Q4: Describe the main differences between VLOOKUP and XLOOKUP. Given a choice, when would you still use VLOOKUP instead of XLOOKUP?
✓ XLOOKUP improves on VLOOKUP in every dimension: (1) lookup_array and return_array are independent ranges, so the search column does not need to be leftmost and the return column can be anywhere including to the left; (2) there is no col_index_num that breaks when columns are inserted; (3) the [if_not_found] argument replaces the IFERROR wrapper; (4) search_mode -1 finds the last match; (5) match_mode 2 supports wildcards; (6) return_array can span multiple columns, returning several fields at once. You would still use VLOOKUP when: the workbook must work in Excel 2019 or earlier (XLOOKUP returns #NAME? in older versions); when sharing files with users whose Excel version is unknown; or in organizations where all staff must be able to understand and audit formulas and may not be familiar with XLOOKUP syntax.
Q5: Write a two-way lookup using INDEX+MATCH+MATCH. The scenario: a product sales table has product names in column A (A2:A20) and month names across the top (B1:M1), with sales figures in B2:M20. Find the sales for the product in cell P2 during the month in cell Q2.
✓ =INDEX($B$2:$M$20, MATCH(P2,$A$2:$A$20,0), MATCH(Q2,$B$1:$M$1,0)). INDEX returns the value from the data range $B$2:$M$20. The first MATCH(P2,$A$2:$A$20,0) finds the row position of the product named in P2 within column A. The second MATCH(Q2,$B$1:$M$1,0) finds the column position of the month named in Q2 within row 1. INDEX then returns the value at the intersection of that row and column. This is the standard two-way lookup formula — XLOOKUP does not natively do two-way lookup without nesting, making INDEX+MATCH+MATCH still valuable even in Excel 365.
Q6: Why is ROUND(B2*0.15,2) better than simply formatting B2*0.15 with 2 decimal places when calculating VAT for an invoice?
✓ Formatting only changes how a number is displayed — the underlying value remains the full floating-point result (e.g., 74.999999999 or 75.000000001). When formatted, it may display as "75.00" but calculations using this cell continue with the unrounded value. If you sum many such formatted-but-not-rounded VAT amounts, the total can differ from the sum of the displayed values by several cents — creating reconciliation problems and potential SARS compliance issues. ROUND(B2*0.15,2) actually changes the stored value to exactly 75.00 (two decimal places). The sum of ROUND-ed amounts always matches the sum of the displayed values exactly. For VAT invoicing and payroll, always ROUND deduction amounts rather than just formatting them, to ensure cent-level accuracy required by SARS regulations.