To use XLOOKUP in Google Sheets, give it the value you want to find, the range to search and the range to return from. For example, =XLOOKUP(E2,A2:A20,C2:C20,"ID not found") searches column A for the ID in E2 and returns the value from column C on the matching row.
Start with an exact match. Make the search and return ranges line up, check that each ID identifies the intended record, and test one known match and one missing ID. If an ID occurs more than once, the default search returns the first match in the supplied range.
- Search one row or column of keys.
- Line up the search and result ranges.
- Use the missing-value argument for a readable no-match result.
- Check duplicates before treating the returned value as authoritative.
- Lock source ranges when copying a formula down.
Build a first lookup that you can check by hand
Put Item ID, Item and Location in A1:C1. In row two enter EQ-01, Tripod and Store A. In row three enter EQ-02, Timer and Store B. Put EQ-02 in E2 and the following formula in F2.
=XLOOKUP(E2,A2:A3,C2:C3,"ID not found")The expected result is Store B. Change E2 to EQ-01 and the result should be Store A. Change E2 to EQ-99 and the result should be ID not found. These three tests check the normal path, a second known row and the missing-key message.
The first argument is the search key. The second is the list of possible keys. The third is the list of possible answers. The fourth is what you want to see when there is no match. The formula matches positions within the two ranges. If the second item in the search range matches, Sheets takes the second item from the result range.
Separate the lookup key from the label a reader sees
A description like Timer might be shared by several items. An equipment ID can identify one item even if its description changes. If your register already has a reliable identifier, look up that identifier. Put the human-readable description in the result or in a neighbouring field.
Keep the lookup range small enough to understand
Use the rows that belong to the source table. Avoid including an unrelated register or note beneath the data. A fixed range can be appropriate for a short exercise. If the table will grow, decide how new rows will be included and verify the range after adding a record near the boundary.
Return information to the left of the lookup column
XLOOKUP takes separate search and result ranges. The result can be to the left or right of the search range. If column B contains unique item labels and column A contains their IDs, the following formula searches B and returns A.
=XLOOKUP(E2,B2:B20,A2:A20,"Item not found")Check that the label is actually unique before using it as the key. A left lookup does not solve ambiguity. Two items with the same label can have different IDs. Use a unique ID or reconcile the source if the report must identify one of them.
Return several fields from the matched row
If the source key is in A2:A20 and you want both the label and location, the result range can span columns B and C. Put this formula where two adjacent output cells are free.
=XLOOKUP(E2,A2:A20,B2:C20,"ID not found")The answer can expand across the row. Check both headings in the output. If a nearby cell contains a note or another formula, move the lookup to an appropriate blank area. On a missing key, the formula returns the missing-value message, so design the report to make that state clear.
Use one lookup per output when the fields are far apart
A source may contain a label in column B and a verified status in column H. Separate lookups can be easier to read if the intervening fields do not belong in the report. Keep both formulas tied to the same key and source rows. Test a record whose label and status you can verify independently.
Handle missing IDs and blank inputs separately
A blank selector and an unknown ID are different problems. A blank asks the user to enter a value. An unknown ID should prompt a source check. Use a guard before the lookup when the input is optional or when the search range contains unused blank rows.
=IF(E2="","Enter an ID",XLOOKUP(E2,A2:A20,C2:C20,"ID not found"))Test an empty E2, an existing ID and a new ID that has not been added to the master table. If the result is wrong, inspect the exact key first. A polite message does not establish that the master table is complete.
A matched row can still have a missing result
The missing-value argument applies when the key cannot be found. An existing row with an empty location is a data-quality issue in the matched record. Check the source cell and decide whether the report should show a separate message for incomplete records. Do not use an unknown-ID label for a record you can find.
Keep error messages visible while testing
Avoid wrapping every formula in a broad error handler before you verify its ranges. A range mismatch or blocked output requires a different repair from a missing ID. Start with XLOOKUP and its missing-value argument. Check the actual error when the result is unexpected. Add only the handling the report needs.
Check what happens when a key occurs more than once
Suppose EQ-02 appears twice with locations Store B and Lab 2. The default search proceeds from first to last. It returns the first matching entry. That is useful to know, and it does not tell you which location is correct. Review the record policy before relying on it.
Last match can be deliberate when the order is meaningful
In an append-only log where each later row represents a later update and the order has been verified, you may want the last matching row. Use exact match mode 0 and reverse search mode -1.
=XLOOKUP(E2,A2:A20,C2:C20,"ID not found",0,-1)If the source was sorted by location or pasted out of order, the bottommost entry may not be the latest update. Check timestamps or other authoritative ordering before using this pattern. If every matching record is needed, use a filter that can return several rows.
Inspect a known duplicate during review
Build a harmless test with two entries that share one key and have different locations. Compare default and reverse search. Then change the order and see how the returned location changes. This demonstrates why a lookup can be correct for its search rule and still be wrong for a decision based on recency.
Verify the master register before presenting the result
If the table is meant to contain one current record for each ID, highlight repeated IDs and reconcile them. Some duplicates are legitimate history rows, and a current register may need a separate approved view. Keep the lookup formula simple once the source answers a clear question.
Copy a lookup without moving the master ranges
When you fill the formula from F2 to F3, the key should move from E2 to E3. The source table should stay fixed. Dollar signs can lock the source references.
=XLOOKUP(E2,$A$2:$A$20,$C$2:$C$20,"ID not found")After filling down, open the formula in a later row and compare the references. Check a record near the bottom of the master list. A shifted lookup can appear to work on the first rows and fail only when a key lies outside the shifted range.
Point to the correct worksheet
If the master data is on a tab named Equipment, use explicit references. The selector in E2 belongs to the sheet containing the formula in this example.
=XLOOKUP(E2,Equipment!$A$2:$A$20,Equipment!$C$2:$C$20,"ID not found")If the sheet name contains spaces, quote it in the sheet reference. Verify the active workbook and tab. When copying a report to another file, check whether the master data moved with it or whether the formula needs a different approved source.
Diagnose values that look identical but do not match
Check the two actual cells, not just their display. A leading or trailing space can make a text key behave differently. A code displayed with leading zeros might be stored as a number. Check what the source specification requires before changing the type. If 0042 and 42 are different codes, preserve them as distinct identifiers.
Compare where the ranges begin and end
Search A2:A20 and return C2:C20 when each row is one record. If the return range starts on row three, the ranges can have different lengths or pair unrelated values. Check the first row, final row and expected record alignment. Equal-sized ranges can still be wrong if they are offset by a row.
Check a value that came from an import
Compare the imported key with the approved source. Clean accidental spacing in a working column where appropriate. Preserve intentional punctuation. If the import already converted an identifier, return to the source and use a text-preserving workflow where required. Test a normal key and one with leading zeros.
Use approximate matching only when the rule calls for it
A pricing band or score band can deliberately match a threshold rather than a literal ID. Google documents match modes for exact, next smaller, next larger and wildcard matching. Use an approximate mode only when the table represents those boundaries. For a sorted table of minimum scores in A2:A5 and labels in B2:B5, exact or next smaller can represent the lower boundary of a band.
=XLOOKUP(E2,A2:A5,B2:B5,"Below first threshold",-1)If thresholds are 0, 50, 70 and 90, a value of 69 should use the band beginning at 50. A value of 70 should match the 70 band. Verify exact boundaries, values between thresholds and any inputs beyond the supported range. Use a separate guard for an upper limit when the task requires one.
Keep threshold and search modes distinct
The fifth argument controls matching behaviour. The sixth controls search direction or search method. A minus one in the fifth position is a different choice from minus one in the sixth. Review the argument order when editing a formula. Avoid binary search modes unless the source is sorted in the order required by the selected mode.
Choose XLOOKUP, FILTER or a summary formula
Use XLOOKUP when you want one matching record’s value. Use FILTER when the question is “which rows match?” and several results may be valid. Use an aggregation such as SUMIFS when the goal is the total of the matching amounts. A lookup that returns the first amount will not total the other records.
For an activity register, XLOOKUP can retrieve a student’s approved name from a unique ID table. FILTER can show every session for that student. SUMIFS can total the hours of sessions meeting a status rule. Keep those three outputs separate. They answer different questions even when they use the same source data.
Plan a reliable key before building a lookup form
Identify the kind of record the result should describe. An inventory register can use an item ID. A class roster can use a school-approved identifier. An event table may need both a participant ID and a session ID because the same participant can book several sessions. If a key only identifies a person, a lookup cannot decide which session the reader meant. Decide the level of detail before writing the formula.
Identify a record with a pair of fields
If the source has a reliable combined key, search that key directly. For a small example, put item IDs in A, storage area in B, and quantity in C. A row can have the same item ID in two storage areas. In a working column D, build an approved combined key with a separator that cannot appear in either source field. For example, use A2&"|"&B2 only when the bar is excluded from both field values. If the desired item and area are in F2 and G2, use this formula.
=XLOOKUP(F2&"|"&G2,D2:D20,C2:C20,"Item and area not found")Test the same item in two locations. Verify that the combination returns a different quantity for each. Check a missing location and an incomplete key. If the records can contain the separator, use a different encoding or a query with separate conditions. Avoid a bare concatenation such as AB plus C and A plus BC. Those produce the same string without a safe boundary.
Keep names available for the reader
A lookup form can show an approved ID selector and return the item name, location and owner. The ID lets the formula distinguish records. The labels let the reader verify that they chose the right item. A useful practice is to return enough context to catch a wrong selection. For example, two similar tripods may be stored in different rooms. Showing the name and location next to the key makes the difference visible.
Decide how an incomplete record is reported
A key can be valid while its record is not ready to use. An item might exist with a blank location or a pending approval. Use a field that describes that state. In a larger report, show the ID match separately from the missing field. This lets the reader distinguish a typo in the selector from a master record that needs maintenance. Verify both cases in a test area.
Look across a row when the source uses column headers as keys
XLOOKUP can also search horizontally. Suppose B1:E1 contains Monday, Tuesday, Wednesday and Thursday, and B5:E5 contains the number of bookings for each day. If G1 contains Wednesday, use this formula.
=XLOOKUP(G1,B1:E1,B5:E5,"Day not found")The search range is one row. The result range runs across the same columns. Check Monday and Thursday to catch an offset. This pattern can help with a compact timetable. If the sheet stores repeated headers such as several Mondays, use a more precise label or another data structure. A header alone must identify the intended column.
Return a column of related information where appropriate
The return range can include multiple rows when it is aligned with a horizontal lookup range. If B2:E4 contains three details per day, replacing B5:E5 with B2:E4 can return the matched column of details. Put the formula above a clear output area and label each result row. Test the first and last source columns. If a cell below is occupied, move the result rather than deleting data without checking it.
Check copied headers
A timetable can contain Week 1 and Week 2 sections with the same day labels. If you accidentally search all the Monday cells in a wider header range, the formula may return the first Monday regardless of week. Choose the appropriate source block or use an approved key that includes the week. Review a known date rather than relying on the label alone.
Diagnose the four common ways a copied lookup goes wrong
A lookup can return a wrong value, show a missing-key message, report a formula error or be unable to place a multi-cell result. Start with the exact symptom. Find the key in the source table directly. Check its position and the intended result on the same row. Then inspect the formula’s range boundaries and argument order.
The wrong item name appears for a correct key
Look for repeated keys and inspect the offset of the return range. If the search starts in A2 and the result starts in C3, matching the first key can return the second source row’s information. If both ranges start at row two and the key repeats, inspect the search order. Choose the intended record rule before changing the formula.
The last few records always fail
Compare the fixed source range with the bottom of the register. A formula ending at row 20 cannot search row 21. If only formulas farther down the output fail, inspect relative references. Lock master ranges when the source should not move. Test the newest known record after the repair, along with the earliest one.
Only identifiers from the new import fail
Compare type and spacing with the existing keys. Inspect a record with leading zeros and one that contains punctuation. If the importer changed their meaning, return to the approved original and correct the import or its staging data. Avoid stripping punctuation or converting every code to a number just to force matches. A forced match can join records that the issuer considers different.
A multi-column result cannot expand
Check the cells to the right of the formula and whether any are occupied. Move the lookup or clear only the intended destination. Verify all returned fields after changing its location. A result blocked by an old note is a layout problem. Changing the lookup key will not clear it.
Use a threshold table and test every boundary
Create a harmless practice table with minimum scores 0, 50, 70 and 90. Give each threshold a distinct label such as Band A through Band D. Use exact or next smaller and verify scores of 0, 49, 50, 69, 70, 89 and 90. Each score should use the greatest minimum it meets. Check -1 separately so you can see the missing-value result.
Decide what happens outside the allowed input range
If scores must be between 0 and 100, the threshold formula alone may still assign 105 to the highest band. Add an explicit check if such a score should be rejected. A threshold table answers which boundary is met. It does not automatically know the allowed domain. Test values below and above the permitted range as well as ordinary inputs.
Check different meanings for upper and lower boundaries
A table of minimum values uses a different rule from a table of upper limits. If the entries say “up to 10,” “up to 20” and “up to 50,” the intended match might be the exact or next larger boundary. Read the table’s definition and test exactly 10 and just above 10. Choose the matching mode only after that definition is clear.
Use ordinary exact matching for identifiers
Approximate matching can return a nearby key when an identifier does not exist. That is useful for bands and inappropriate when a code identifies one record. Keep exact matching for IDs, names and statuses unless the task explicitly defines another rule. Test a missing value that looks similar to a real one so you can confirm the lookup does not silently choose a neighbour.
Compare XLOOKUP with other common lookup workflows
With VLOOKUP, an older formula can identify its return field with a column number within a selected range. In an XLOOKUP formula, the return range appears directly. When converting a report, identify the actual search column and return column from the old formula before rewriting it. Check its exact or approximate match behaviour too. Use the appropriate match mode in the new formula.
Verify the old and new results on the same rows
Compare three examples: an existing unique ID, an ID at the end of the range and a missing ID. If the old and new formulas disagree, inspect whether one used approximate matching or a different source range. Check which result the task actually requires. A formula conversion is an opportunity to find a mismatch, not a reason to silently change the definition.
Keep FILTER for lists and SUMIFS for amounts
A school club may have several approved transactions. A lookup for the club name can return one transaction, and that can look like the club’s total if the heading is vague. Use FILTER for all transactions or SUMIFS for the total. Put an explicit label above each result. If you use a lookup on a summary table that already contains one row per club, verify the summary’s date window and filters.
Check the version you are actually using
Google Sheets documentation distinguishes the ordinary spreadsheet function from variants used with connected data. If you are working with a connected source, use the supported syntax for that environment. The examples here assume ordinary Google Sheets cell ranges. Check the function help when a control or argument behaves differently in your file.
Build a small lookup dashboard and verify the handoff
Create an Equipment tab with one approved row per item and a separate Report tab. Put an item selector on Report and use XLOOKUP to show the item name and location. Add a prompt for an empty selector. Test an active item, an unknown key, a record with a missing location and a repeated key in a harmless copy. Review what the reader sees in each case.
Check a changed value and a newly added record
Update a location in the source and verify that the report reflects it. Add a new item at the end of the source and check whether the range includes it. If the range is intentionally fixed, update it with both the lookup and return ranges aligned. If the report uses a dropdown, check that the new ID also appears in its source list.
Keep results tied to the intended source
If another editor copies the report, ask which master table it should read. Verify the references and the data owner’s approved sharing route. A filtered or looked-up display does not restrict access to a full underlying sheet. When the recipient should receive only the approved result, use the appropriate separate delivery workflow. Verify the delivered values against the current record before sending.
Record the smallest useful control checks
A known match should return the expected label. A missing key should show the intended message. A duplicate should trigger a review or follow a documented, verified order. Add one record near the lower boundary and confirm it is included. These four checks are more informative than looking at several correct results from the top of the same list.
Review a lookup that will be used in an application or report
A form may ask the reader to choose an identifier and display related information before they submit. Check whether the lookup result is evidence or just a convenience. If the information must be current, verify the source and its update process. If the record must reflect a particular date, use the appropriate approved source for that date. A live lookup can show today’s value even when the reader needs last month’s record.
Use a lookup to check consistency between two lists
Suppose a class register has student IDs and an activity list uses the same IDs. Return a name or approved class from the register and compare it with the activity list. A missing result can indicate a typing problem, a source range problem or a record that has not been added. Review each category separately. Keep the activity record intact while you investigate instead of overwriting it with a guessed match.
Confirm what happens after sorting the source
Sort the complete source table under the approved workflow. An exact lookup on a unique key should still return the same related fields if every row stayed intact. If the result changes, check for duplicate keys or a sort that moved only one column. A reverse search can intentionally change which duplicate it selects after sorting. Verify the order again before using it to select a current record.
Write down the field that is being returned
Label the result precisely. An item may have both a current location and a planned destination, or a person may have a current and former class. Check the result range against the label. Use a known case where the two fields differ. This catches a correct search that returns the wrong column.
Check a blank value without confusing it with zero
An empty source field, an actual zero and a missing key can carry different meanings. Test each one with your source and display settings. If the reader needs an explicit status, use an appropriate check rather than interpreting an empty-looking output by sight. A matched item with zero stock is a real record. An unknown item has no matching record. The report should communicate that difference.
Preserve a reproducible result when handing it off
If the task requires a fixed record, make the authorised snapshot after checking the live lookup and its source. Record the selected key and the relevant version or date where required. Keep the editable worksheet separate from the delivered copy. If a correction is needed later, use the approved source and verify the replacement before sharing.
Frequently asked questions
What is the simplest XLOOKUP formula?
Use XLOOKUP with a search key, a lookup range and a result range. Add a missing-value message if helpful.
Can XLOOKUP return a value on the left?
Yes. Choose the result range independently and keep it aligned with the lookup range.
Does it return all matching rows?
A typical XLOOKUP returns the selected match. Use FILTER when you need every matching row.
Why does the formula work only on the first row?
Check whether the source ranges moved when you filled down. Lock them when the master table should remain fixed.
Why does a known ID say it was not found?
Check type, spacing and the rows included in the lookup range. Compare the actual values with the source.
How do I get the last match?
Use reverse search mode -1 in the sixth argument and verify that the source order has the intended meaning.
Helpful reading
- Google Docs Editors Help on XLOOKUP
- How to Highlight and Remove Duplicates in Google Sheets
- How to Use FILTER in Google Sheets
- How to Use SUMIFS in Google Sheets With Multiple Criteria
- How to Create a Pivot Table in Google Sheets
- How to Add and Count Checkboxes in Google Sheets
Verify the key and the returned record
Check an existing key, a missing key and any repeated key before using the lookup in a shared report.