This page focuses on just one simple, complete VLOOKUP formula example, explained slowly enough that every part of it makes sense before moving on. It uses a small invoice table, since matching a client name or amount to an invoice number is a common real-world use case.
By the end, the same example can be adapted to any similarly structured table just by swapping in different column references.
| Invoice No | Client | Amount Due | Due Date |
|---|---|---|---|
| INV-3301 | Sundar Traders | 24500 | 12-Sep-2026 |
| INV-3302 | Kavya Textiles | 18900 | 20-Sep-2026 |
| INV-3303 | Om Enterprises | 31200 | 28-Sep-2026 |
This formula searches for "INV-3301" in column A, looks across the range A2:D4, counts two columns over to Client, and requires an exact match.
- "INV-3301" is the exact invoice number being searched for.
- A2:D4 is the full invoice table, starting at the Invoice No column.
- 2 counts to the Client column, the second column in that range.
- FALSE means only an exact invoice number match is accepted.
Changing only col_index_num from 2 to 3 returns Amount Due instead of Client for the same invoice:
For a handful of invoices, typing this formula for each one is manageable. For an entire invoice register, Turbo Excel VLookup applies the same simple matching logic across every row at once, without the formula needing to be copied or adjusted per invoice.
A surprising number of VLOOKUP problems turn out to be a data type mismatch rather than a logic error - a value stored as text in one column and as a number in the other will never match, even if they look identical on screen.
Before assuming the formula itself is wrong, select the lookup value's cell and check the small green triangle or the number alignment; numbers align right by default, and text aligns left, which is a fast way to spot the mismatch.
In a shared workbook, a bare VLOOKUP formula gives the next person no indication of what it's supposed to be matching or why. Adding a cell comment, or a short label in an adjacent column, explaining the source and purpose of the lookup saves significant time when the workbook is revisited months later or handed to a colleague.
This becomes even more important once a workbook has several lookups feeding into each other, since untangling a chain of undocumented formulas is far harder than reading a documented one.
What does this example formula search for?
The exact invoice number "INV-3301" within the leftmost column of the selected range.
Why is col_index_num set to 2 in this example?
Because Client is the second column, counting from the start of the selected table_array.
How would I change the formula to return Amount Due instead?
Change col_index_num from 2 to 3, since Amount Due is the third column in the same range.
Why does the example require an exact match?
Invoice numbers are unique identifiers, so an approximate match could return the wrong invoice's details.
Can this same table_array be reused for multiple lookups?
Yes, the same A2:D4 range can support several VLOOKUP formulas, each returning a different column.
What if I have hundreds of invoices to look up at once?
Turbo Excel VLookup matches an entire invoice list in a single action instead of one formula per invoice.
Is it safe to have duplicate values in the lookup column?
A lookup formula will only return the first matching row it finds, so duplicates can silently return the wrong record if not accounted for.
Should I test a new formula on one row before applying it everywhere?
Yes, verifying a single result against the source data first catches setup mistakes before they're copied across an entire column.
Is it a good idea to edit source data while formulas are actively referencing it?
It's safer to keep a stable source sheet separate from a working sheet, since editing referenced data mid-use can produce confusing intermediate results.
Why would a text-formatted number fail to match a real number?
Excel treats text and numeric values as different data types, so "101" stored as text won't match 101 stored as a number even though they display the same.
Ready to stop fighting #N/A errors and match your spreadsheets visually?