Written and maintained by CASRAI Editorial Board
Published · Last updated
On this page
Short answer: the lookup value is the item you hand to a lookup function so it can find the matching row or column in your data, such as a sample ID, participant code, DOI or grant number. In Excel it is the first argument of both VLOOKUP and XLOOKUP. This guide shows where it goes, how to match it exactly, and how to fix the errors that appear when a lookup value is not found.
It is written for researchers and analysts who merge a list of identifiers with a metadata table in a spreadsheet and need the match to be correct, not merely plausible.
What the lookup value is and what it is used for in research
Microsoft’s documentation describes the VLOOKUP lookup_value as “the value you want to look up”, and it must sit in the first column of the table you search. In XLOOKUP it is the value to search for in the lookup array. Typical research uses are joining sample IDs to collection metadata, attaching a funder or journal name to a list of codes, and checking that two exports describe the same records. Good lookup keys are the kind of stable identifiers discussed in metadata practice and in a data management plan.
Excel lookup value, lookup values in Excel, lookup value in Excel: the same idea
Searches for “excel lookup value”, “lookup values excel” and “lookup value in excel” all refer to this first argument. The argument name differs by program, which matters when you move a formula between tools:
| Program | Function | Name of the value you search for |
|---|---|---|
| Microsoft Excel | VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) |
lookup_value |
| Microsoft Excel | XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) |
lookup_value |
| Google Sheets | VLOOKUP(search_key, range, index, [is_sorted]) |
search_key |
| Google Sheets | XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode]) |
search_key |
| LibreOffice Calc | VLOOKUP(Lookup; Array; Index [; SortedRangeLookup]) |
Lookup |
| LibreOffice Calc (24.8 and later) | XLOOKUP(Search criterion; Search Array; Result Array [; [Result if not found] [; [Match Mode] [; Search Mode]]]) |
Search criterion |
Where to find the lookup functions (versions and access)
VLOOKUP is a long-standing Excel function. Microsoft’s XLOOKUP page carries the note that XLOOKUP is not available in Excel 2016 and Excel 2019; it is available in Microsoft 365, Excel 2021 and Excel 2024. If you share workbooks with colleagues on older versions, use VLOOKUP (or INDEX with MATCH) so the formula does not break. Get Excel and its documentation only from Microsoft; Google Sheets and LibreOffice Calc are covered under free alternatives below.
Step by step: look up a value in Excel
The example below is an illustrative composite, not real study data. Sheet “Samples” lists sample IDs in column A. Sheet “Metadata” has the sample ID in column A, collection site in column B and collection date in column C. The goal is to pull each sample’s collection date into “Samples”.
Option 1: XLOOKUP (Microsoft 365, Excel 2021, Excel 2024)
- In “Samples”, click cell B2, where the first result should appear.
- Type
=XLOOKUP(A2, Metadata!A:A, Metadata!C:C, "not found")and press Enter. A2 is the lookup value;Metadata!A:Ais where Excel searches;Metadata!C:Cis what it returns; “not found” is the optionalif_not_foundtext. - Copy the formula down the column.
Per Microsoft, match_mode defaults to 0 (exact match), and the return column may be on either side of the lookup column. Omit if_not_found and a missing value returns #N/A.
Option 2: VLOOKUP (all current versions)
- In B2, type
=VLOOKUP(A2, Metadata!$A:$C, 3, FALSE)and press Enter. - Here 3 means the third column of the table array, counted from 1 at the leftmost column, so the sample ID must be in column A and the date to its right.
- Copy the formula down. The dollar signs (absolute references) keep the table fixed, which Microsoft lists as a best practice.
Always end with FALSE for identifiers. Microsoft states that if range_lookup is omitted or TRUE, VLOOKUP does an approximate match and the first column must be sorted ascending; FALSE (or 0) requires an exact match. Google’s Sheets documentation likewise defaults is_sorted to TRUE and recommends FALSE. Matching IDs approximately can return the wrong row without any error.
Wildcards in the lookup value
With an exact VLOOKUP (FALSE), Microsoft notes that ? (one character), * (any run of characters) and ~ (to search for a literal ? or *) can be used in a text lookup value. In XLOOKUP, wildcard matching is switched on with match_mode 2.
Reading the output
- First match only. XLOOKUP returns the first match it finds (search_mode 1, first to last, is the default; -1 searches last to first). VLOOKUP and Google’s VLOOKUP also return only the first match. If an ID appears twice in the metadata, you will not see the duplicate; check for duplicate keys before trusting a merge.
- Several columns at once. XLOOKUP can return more than one item. Microsoft’s example
=XLOOKUP(B2,B5:B14,C5:D14,0,1)returns two columns from the matched row. - Blank lookup value. If lookup_value is omitted in XLOOKUP, it searches for blank cells in the lookup array rather than failing.
- Binary search modes. search_mode 2 and -2 expect sorted data; Microsoft warns that unsorted data gives invalid results. Leave them alone unless you have a very large sorted table.
Troubleshooting a lookup value that will not match
| Symptom | Likely cause (per vendor documentation) | Fix |
|---|---|---|
#N/A |
With exact match, the value is not in the first column; with approximate match, it is smaller than the smallest value. | Check the key exists; in XLOOKUP add if_not_found. In Google Sheets, wrap with IFNA(). |
| Wrong value returned | VLOOKUP left on approximate match with an unsorted first column. | Sort the first column ascending or use FALSE. |
#REF! |
col_index_num is larger than the number of columns in table_array. | Widen the table array or lower the column number. |
#NAME? |
A text lookup value typed directly into the formula is missing quotation marks. | Write "S-001", or reference a cell. |
#VALUE! |
The column index argument is not valid (Google: less than 1 or not a number). | Use a whole number of 1 or more. |
| Visible match, not found | Numbers or dates stored as text, or stray spaces and nonprinting characters. | Store numbers and dates as numbers; clean text with TRIM and CLEAN. |
| Need to match on two columns | VLOOKUP takes a single lookup value. | Google’s documentation suggests a helper column that combines the criteria. |
Lookup merges are quiet sources of error in analysis files. If your spreadsheet supports a regulated process, see spreadsheet validation for GxP; if the merged table is going into R, see how to import data in R.
Free alternatives
| Tool | Vertical lookup | XLOOKUP |
|---|---|---|
| Google Sheets | VLOOKUP(search_key, range, index, [is_sorted]) | Yes: XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode]); the lookup range must be a single row or column |
| LibreOffice Calc | VLOOKUP(Lookup; Array; Index [; SortedRangeLookup]) | Yes, from LibreOffice 24.8: XLOOKUP(Search criterion; Search Array; Result Array [; [Result if not found] [; [Match Mode] [; Search Mode]]]); the search array must be one-dimensional and on one sheet. On older versions, use INDEX with MATCH |
FAQ
What is the lookup value in Excel?
It is the first argument of VLOOKUP and XLOOKUP: the value Excel searches for, usually a cell reference such as A2 or a quoted text string.
Why does VLOOKUP return the wrong result?
Most often the fourth argument was left out. Omitted or TRUE means approximate match, which needs a sorted first column; add FALSE for an exact match.
Can the lookup value be to the right of the data I want?
Not with VLOOKUP, which needs the value in the first column and returns from columns to its right. XLOOKUP can return from either side of the lookup column.
Is XLOOKUP available in my version?
Microsoft notes it is not available in Excel 2016 and Excel 2019. It is in Microsoft 365, Excel 2021 and Excel 2024.
How do I stop #N/A from showing?
In XLOOKUP supply the fourth argument (if_not_found). In Google Sheets VLOOKUP, wrap the formula in IFNA(). Hiding #N/A does not fix a missing key, so investigate first.
Sources
- Microsoft Support, XLOOKUP function (read 2026-11-11).
- Microsoft Support, VLOOKUP function (read 2026-11-11).
- Google Docs Editors Help, VLOOKUP and XLOOKUP (read 2026-11-11).
- LibreOffice Help, Spreadsheet functions: lookup (read 2026-11-11).







