Vlookup Calculator
You have a small table in front of you and you need one cell out of it: the name that belongs to ID 102, the colour that belongs to a product code. This calculator runs that lookup in the browser, with no spreadsheet open. Paste the table, type the value to find, pick which column to bring back, and choose an exact or a partial search.
Rows are read one per line and columns are split on semicolons only. A column number wider than the table returns a distinct out-of-range message instead of “Not found”, because every row is shorter than the column asked for. Excel shows #REF! for the same request.
Quick Answer
With the pre-filled table (101;Apple;Red, 102;Banana;Yellow, 103;Grapes;Green), a lookup value of 102 and column 2 on an exact match, the calculator returns Result: Banana. The format that makes it work is strict: one row per line, and a semicolon between the columns of each row. Commas never separate anything in this tool.
Key takeaways
- Rows go one per line and columns are split on semicolons. A table typed with commas, the way the old label on this page suggested, returns “Not found” every time.
- Exact Match = Yes compares the whole first-column cell, letter case included. Exact Match = No runs a partial (contains) search of the first column. That partial search is not Excel’s approximate match, and the section below shows the difference.
- The search only ever reads the first column, and only ever returns the first matching row, exactly like the spreadsheet function it mirrors.
- A column number beyond the width of the table returns a distinct out-of-range message here, naming the column and the table width. Excel answers the same request with #REF!, so check your column count when a lookup surprises you.
- Quick Answer
- How to use the VLOOKUP calculator
- The VLOOKUP formula this tool mirrors
- Worked example: finding Banana in a three-row table
- Exact and partial search here vs VLOOKUP in Excel
- What competing calculators miss
- Why a lookup returns “Not found”: a short checklist
- Frequently asked questions
- Add This Calculator to Your Website
- References
How to use the VLOOKUP calculator
Four entries and one click. The result stays hidden until you calculate.
- Enter the table data: one row per line, with a semicolon between the columns of each row, for example
102;Banana;Yellow. The first column is the one that gets searched, so put your IDs, codes or names there. The three-row fruit table is pre-filled. - Enter the lookup value: the exact text or number to find in the first column. The example uses 102.
- Enter the column number to return: 1 brings back the first column itself, 2 the second column, 3 the third. The example uses 2.
- Set Exact Match to Yes or No: Yes needs the whole cell to match. No finds the first first-column cell that contains your text anywhere inside it.
- Click Calculate: the result line shows “Result: …” with the cell from the matching row, or “Result: Not found” when no row qualifies.
Blank table data, a blank lookup value, or a column entry that is not a number is rejected with “Please enter valid data.” A column number below 1 gets the same message. Copy in data you copied from a spreadsheet needs one fix first: spreadsheet pastes arrive tab-separated or comma-separated, and neither separator is read here, so replace the separators with semicolons and put each row on its own line before you calculate.
The VLOOKUP formula this tool mirrors
VLOOKUP, short for vertical lookup, searches the first column of a table for a value and returns a cell from the same row. Microsoft documents the Excel form as VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]), and Google Sheets uses the same four-part shape with its own help page describing identical behaviour.
lookup_value: what to find in the first column · table_array: the table to search · col_index_num: which column to return, counting from 1 · range_lookup: FALSE for exact match, TRUE for approximate match
This calculator maps onto the first three arguments directly: your lookup value, your pasted table, and your column number. The fourth argument is where the tool deliberately differs, and the difference matters enough to get its own section below. In Excel, range_lookup FALSE is an exact match and TRUE is an approximate match over a sorted column. Here, Yes is an exact match and No is a contains search. Same slot in the formula, different behaviour in the No case.
One more mapping worth knowing: Excel counts the return column from the left edge of the table_array you select, and so does this tool. Column 2 of the pre-filled table is the fruit name column, which is why lookup 102 with column 2 returns Banana rather than Yellow.
Worked example: finding Banana in a three-row table
The pre-filled table lists three products by ID, name and colour. The worked example asks for the name (column 2) of product 102, on an exact match.
The same table, run through five more lookups, shows how each mode behaves. Every row below is a real run of this calculator, so you can retype any of them and get the same answer back.
| Lookup value | Column | Exact match | Result | Why |
|---|---|---|---|---|
| 102 | 2 | Yes | Banana | Row 2 matches exactly; column 2 is the name |
| 102 | 3 | Yes | Yellow | Same row, third column is the colour |
| 10 | 2 | No | Apple | Partial search: “101” contains “10”, and row 1 comes first |
| 999 | 2 | Yes | Not found | No first-column cell equals 999 |
| 102 | 5 | Yes | Not found | The table has 3 columns; every row is shorter than column 5, so all rows are skipped |
Exact and partial search here vs VLOOKUP in Excel
The honest summary: this tool copies Excel’s exact match closely, apart from letter case, and its “No” mode is a different feature from Excel’s approximate match. Anyone who has fought a spreadsheet lookup knows the modes are where wrong answers hide, so here is the side-by-side.
| Behaviour | This calculator | Excel / Google Sheets VLOOKUP |
|---|---|---|
| Exact match | Yes: first-column cell must equal the lookup value, case included (“apple” does not match “Apple”) | FALSE: exact match, and case does not matter (Microsoft documents uppercase and lowercase as equivalent) |
| The other mode | No: partial search. The first row whose first column contains your text wins, in table order | TRUE: approximate match. Returns the largest first-column value that is less than or equal to the lookup value, and needs the column sorted ascending |
| Value missing | “Result: Not found” | #N/A error (Google suggests wrapping in IFNA to replace it with your own text) |
| Column too wide | “Result: Not found”, because short rows are skipped | #REF! error, per Microsoft’s VLOOKUP documentation |
| Column below 1 | “Please enter valid data.” | #VALUE! error when col_index_num is less than 1 |
Partial search here behaves more like the wildcard lookups Google Sheets supports inside an exact match (a search text plus “*” patterns) than like a true approximate match: it is a text-contains test, not a nearest-number test. If your real task is “find the rate band for the value just below mine in a sorted price list”, that is Excel’s TRUE mode or a nearest-value estimate, closer to what an interpolation calculator does between two known points. This tool will not do it, and it is better to know that before trusting a result.
What competing calculators miss
Most pages ranking for this search are not lookup tools at all. They fall into a few camps:
- Formula generators (Wand, Toshical, CodePal style pages): you fill in fields and they hand you a finished =VLOOKUP(...) formula to paste into Excel. Useful if you have Excel open and a workbook ready. They never run the lookup, so a wrong column count or a mistyped key produces a formula, not an answer, and the mistake surfaces later inside your spreadsheet.
- Calculator pages that promise Excel’s approximate match: at least one rival lookup calculator offers an “approximate match” mode described as finding the closest match, Excel-style. Read the fine print before trusting it with a sorted rate table: a genuine approximate match has to compare values in order, and several of these tools quietly do something simpler.
- Tutorials with no tool (LibreOffice and Excel help blogs): accurate teaching, worked screenshots, zero interactivity. You still test your understanding by hand.
- The gap this page fills: it actually executes the lookup on the table you paste, states its separator format in the label itself, and publishes the two places its behaviour departs from Excel (case-sensitive exact match, contains search instead of approximate match) instead of letting you discover them from a wrong answer.
The same honesty applies to the spreadsheet family on this site: if your table work is really about dates inside Excel, the Excel date calculator covers that side. If your spreadsheet work is a payment formula rather than a lookup, the Excel auto loan calculator and the Excel car payment calculator build Excel's PMT formula with your numbers filled in, ready to paste into a workbook. This page stays on the single job of finding a row and returning a cell.
Why a lookup returns “Not found”: a short checklist
Nine times out of ten, “Not found” is a formatting problem, not a missing value. Work down this list before assuming the key is absent.
- Commas or tabs as separators. The parser splits rows on line breaks and columns on semicolons, full stop. A CSV-style line like
102,Banana,Yellowis one single cell here, so nothing can match and no column beyond 1 exists. - The whole table on one line. Rows are lines.
101;Apple;Red 102;Banana;Yellowon a single line is one row whose later cells contain spaces and numbers, and its first cell is still 101. - Letter case in exact mode. Exact match compares case, so a lookup of
appleagainst a key ofApplefails here even though the same lookup succeeds in Excel. Match the case, or switch to partial search and type a distinctive fragment. - The key is not in the first column. The search never looks anywhere else. Reorder your pasted table so the key column comes first, the same rule VLOOKUP itself enforces.
- The column number is wider than the table. A 3-column table with column 5 requested now returns a distinct out-of-range message naming the column and the table width, matching Excel's #REF! error, instead of “Not found”. Rows shorter than a requested column that is still within the table width are skipped, as before.
- Duplicate keys. The first matching row wins and the rest are ignored. If two rows share a key, the answer is whichever sits higher in the table.
Frequently asked questions
One row per line, with a semicolon between the columns of each row, like 102;Banana;Yellow. The first column is the column that gets searched. Commas and tabs are not separators in this tool, so data pasted straight from a CSV file or a spreadsheet needs its separators replaced with semicolons first.
It runs a partial search: the calculator returns the first row whose first column contains your lookup text anywhere inside it. It is not the same as Excel’s approximate match, which returns the largest sorted value less than or equal to the lookup value.
Yes, in both modes. An exact lookup of apple will not match a first-column entry of Apple, and the partial search is case-sensitive too. Excel’s VLOOKUP is the opposite: Microsoft documents it as treating uppercase and lowercase text as equivalent.
It no longer says that: a column number wider than the table now returns a distinct out-of-range message naming the column and the table width, matching Excel's #REF! error. The key may well exist; the column does not. Short rows are still skipped when the requested column is within the table width.
No. The search only ever reads the first column, exactly like VLOOKUP in a spreadsheet. Reorder the columns in the table you paste so the value you search by comes first.
The first matching row wins and the search stops. Later rows with the same key are never reached, so sort or trim your table if the duplicate lower down is the one you actually want.
For a quick single lookup or for learning how VLOOKUP thinks, yes. For real workbook work, no: it holds one pasted table at a time, returns one cell at a time, and its No mode is a contains search rather than a true approximate match. Spreadsheet VLOOKUP also handles cell references, ranges and formulas, which a pasted text table cannot.
No. The lookup runs entirely in your browser on the text you pasted. Nothing is uploaded, stored or shared unless you choose to copy or share the result yourself.
Add This Calculator to Your Website
Embed this calculator on your own site with the code below. Pick a size to preview it live, then copy the code.
Powered by Calculator Zilo
References
- Microsoft. WorksheetFunction.VLookup method (Microsoft Learn) (VLOOKUP argument definitions; col_index_num below 1 or beyond the table width produces an error; uppercase and lowercase text treated as equivalent; approximate match returns the next largest value below the lookup value). Accessed October 24, 2026.
- Google. VLOOKUP: Google Docs Editors Help (Sheets syntax, exact vs approximate match examples, #N/A handling with IFNA, wildcard partial matches inside exact-match mode). Accessed October 24, 2026.
- Microsoft Q&A contributors. Please explain VLOOKUP, HLOOKUP and XLOOKUP with some examples (Microsoft Q&A) (col_index_num under 1 returns #VALUE!, beyond the table width returns #REF!, approximate match needs an ascending sorted first column). Accessed October 24, 2026.
- MDN contributors, Mozilla. String: JavaScript reference (MDN Web Docs) (the split and includes string operations this calculator’s parser is built on: rows split on a separator, first-column cells tested for containing the lookup text). Accessed October 24, 2026.
- LibreOffice Help contributors. VLOOKUP with examples in LibreOffice Calc (the same four-argument lookup in the open-source spreadsheet, with search_type 0 for exact and 1 for approximate matching). Accessed October 24, 2026.