← All postsHow Do You Use VLOOKUP in Google Sheets?

How Do You Use VLOOKUP in Google Sheets?

Carlos GarciaCarlos GarciaPublished

How Do You Use VLOOKUP in Google Sheets?

VLOOKUP is the function most people reach for the first time they need to pull a value out of one table and into another. You have a product code in one sheet and a price list in another, and you want the price to appear next to the code without typing it twice.

The function itself is four arguments long and takes about thirty seconds to write. What trips people up is not the syntax. It is the fourth argument, which has a default that Google itself recommends you override, and a set of behaviors that its own documentation describes in two different ways on the same page.

This guide walks through the function exactly as Google documents it, flags where the documentation contradicts itself, and is explicit about the questions Google's VLOOKUP page simply does not answer. Where we could not verify something from Google's own pages, we say so rather than filling the gap.

It is also worth knowing up front that VLOOKUP is not the only lookup function in Google Sheets, and not always the right one. The last section of this guide covers when to reach for something else, and the honest answer is that the decision usually comes down to which direction you need to look rather than to performance.

Free audit

Where do you stand in AI search?

A manual SEO + AI search audit of your site: how ChatGPT, Gemini, Perplexity and Claude read and cite it today, and what to fix first. Report within 48 hours, no credit card.

Get my free audit

Or have it all done: AI SEO services, $999 once

How Do You Use VLOOKUP in Google Sheets? The Short Answer

Google prints the syntax as =VLOOKUP(search_key, range, index, [is_sorted]). The square brackets around the fourth argument mean it is optional, and only that one is optional.

In plain terms: you give it a value to find, a block of cells to find it in, a column number to return from, and a switch that decides whether the match has to be exact. A working example looks like =VLOOKUP(A2, $D$2:$F$50, 3, FALSE), which takes whatever is in cell A2, hunts for it down column D, and returns the matching value from column F.

Two constraints are not negotiable, and both come straight from Google's page. The value you are searching for has to sit in the first column of the range you hand it. And the column you want back has to be at or to the right of that first column. Google words the second one as VLOOKUP only looking at the search key column or columns further right.

That is why the range almost always starts at the column holding your codes or names, not at column A out of habit. If your lookup values are in column D and your prices are in column F, the range is D to F. Starting at column A puts your lookup values in the fourth column of the range, where VLOOKUP will never look.

The fourth argument is where the real decision lives. Leave it off and you get approximate matching, which is a different function in practice from the one most people think they are writing. Type FALSE and you get the exact match that almost every real lookup needs.

Most sites never find out why their best pages do not rank. Get a free SEO audit and see yours.

What Each VLOOKUP Argument Actually Does

search_key

This is the value you are looking for. Google describes it as the value to search for in the search column, and adds that if you expect a non-error value, the search key must be in the first column of the range.

In practice this is usually a cell reference rather than a typed value, because the whole point is to drag the formula down a column and have each row look up its own key. Typing a literal value works, but you only get one answer out of it.

One habit saves time later: point search_key at a cell in the same row rather than at a cell on another tab. A cross-tab search key works, but it makes the formula harder to audit when a column of results looks wrong and you are trying to work out which half of it to blame.

range

The block of cells VLOOKUP searches and returns from. Google says the function searches for the search key in the range's first column, and notes that you can also use a named range.

Named ranges are worth more here than they look. A range called Prices survives someone inserting a column, reads better in a long formula, and spares you the absolute-reference problem below.

Keep the range as narrow as the data actually is. A range stretching to row 1000 when your price list ends at row 50 is not wrong, but Google's own performance guidance points at duplicate and unnecessary data as a cause of slow recalculation, and an over-wide range is the easiest version of that to create by accident.

index

The column number to return, counted from the left edge of your range rather than from column A of the sheet. Google calls this "Column number" and tells you to imagine the columns of the range numbered from left to right starting at 1.

The counting is relative, and that is the part people get wrong. In the range D2:F50, column D is 1, E is 2 and F is 3. The letter on the spreadsheet's own column header is irrelevant once the range has been defined, which is why moving a formula between sheets so often returns the wrong column rather than an error.

Google also sets the bounds, and this is the first place its own page disagrees with itself. The argument description says the smallest possible index is 1 and the largest is the maximum number of columns in that range. The error section a few paragraphs later says the index must be at least 1 and smaller than the maximum number of columns. Those two statements cannot both be true for a range's final column. We have published both as printed rather than guessing which is current, because nothing on the page dates either one.

is_sorted

The optional switch, and the one that causes the most trouble. Google says it is the manner in which to find a match for the search key, and that the two available choices are TRUE and FALSE.

TRUE means approximate match. FALSE means exact match. Google states plainly that TRUE is the default if is_sorted is unspecified, which means an argument most people omit quietly puts the function into its less predictable mode.

There is no middle setting and no fuzzy matching option. Those are the only two values Google documents, so a VLOOKUP that needs to tolerate typos or partial text is either a wildcard search with FALSE, or a different function entirely.

How Do You Write a VLOOKUP Step by Step?

Step 1: Put the two tables where VLOOKUP can see them. Both the lookup values and the table you are pulling from need to be in the same spreadsheet. They can be on different tabs, but if the data lives in another file you need IMPORTRANGE to bring it in first, and Google's own performance guidance warns against nesting IMPORTRANGE inside a lookup.

Step 2: Check that your keys are in the left-hand column of the source table. This is the single most common cause of a VLOOKUP that returns nothing useful. If your product names are to the right of your prices, VLOOKUP cannot reach backwards. Either move the column or use INDEX and MATCH instead.

Step 3: Write the formula in the first data row. Something like =VLOOKUP(A2, $D$2:$F$50, 3, FALSE). Write it in the row that lines up with your first real record, not in the header row.

Step 4: Lock the range with dollar signs. Google explicitly recommends using absolute references for the range. Without them, dragging the formula down shifts the range down with it, and the rows at the bottom of your list end up searching a range that no longer covers the whole table. This produces a column that is right at the top and wrong at the bottom, which is far harder to notice than a column of errors.

Step 5: Set is_sorted to FALSE unless you have a specific reason not to. Google's own words are that it strongly recommends using FALSE for is_sorted, because of its consistent behavior whether or not the search key column is sorted. Type the FALSE rather than leaving the argument off.

Step 6: Fill the formula down and read the error cells rather than hiding them. Errors are information at this stage. Wrap the formula in IFERROR or IFNA only after you understand why each one appeared, not before.

Step 7: Spot-check three rows by hand. Pick one row from the top of the list, one from the middle and one from the bottom, and confirm each returned value against the source table yourself. This takes a minute and catches the two failures that produce no error at all: a range that drifted because it was not locked, and a key that looks identical but is not.

Your competitors are already auditing their sites. Get a free SEO audit and find out what yours is missing.

Why Does Google Recommend FALSE for is_sorted?

Because TRUE does not mean "close enough" in the way most people read it. In approximate-match mode VLOOKUP works down the first column looking for the largest value that is less than or equal to your search key, and it assumes that column is sorted in ascending order. Google's tip is direct about the prerequisite: before you use an approximate match, sort your search key in ascending order, or you may likely get a wrong return value.

The failure mode is what makes this dangerous. An unsorted column with is_sorted set to TRUE does not produce an error. It produces a number, and the number is wrong, and nothing in the spreadsheet indicates it. A column of #N/A is annoying; a column of plausible but incorrect prices is a problem you ship.

Google's sibling page for HLOOKUP adds a detail the VLOOKUP page omits, and it is worth knowing because it applies to the same sorting requirement: correctly sorted numbers should appear as 1, 2, 10, 100 rather than 1, 10, 100, 2. That second ordering is what you get when numbers are stored as text, and it will quietly break an approximate match.

This is the practical reason to distrust a sheet you did not build. Numbers imported from a CSV, pasted from a web page or typed with a leading apostrophe all arrive as text, and nothing in the cell's appearance tells you which of the two you are looking at unless you check the alignment or run ISNUMBER over the column.

There is one case where TRUE genuinely is the right answer. If you are converting continuous numbers into bands, such as mapping an order value to a shipping tier or a score to a grade, approximate match against a small sorted table of lower bounds is exactly the tool for it. That is a deliberate choice about a sorted bin table, not a default you inherited by leaving an argument off.

The test for whether you are in that case is simple. If your lookup table has a row for every possible key, you want FALSE. If it has a row for every band boundary and you expect keys to fall between them, TRUE is doing real work for you.

The performance angle is less clear than you might expect. HLOOKUP's page states that it has much better performance with sorted ranges and is_sorted set to TRUE. Google's dedicated page on improving lookup performance never makes that recommendation for VLOOKUP, never mentions binary search, and never says sorted data is required. We are not going to assume the two pages agree about something only one of them says.

What Google's Own VLOOKUP Page Does Not Tell You

Three absences are worth knowing about before you rely on this function for anything important.

It is silent on case sensitivity. The page never mentions letter case at all. It does not say VLOOKUP is case sensitive, and it does not say it is case insensitive. Google documents case behavior elsewhere for other functions, which makes the silence here conspicuous rather than reassuring. If your keys differ only by case, test it on your own data rather than trusting either answer.

Google's lookup performance page does document case behavior for a different function, which is the comparison that makes the gap visible: when the same documentation set states a rule explicitly in one place and says nothing in another, the silence is not a safe default either way.

It prints two different syntax lines and two different argument names. The top of the page shows the function with all four arguments and no brackets, which reads as though is_sorted is required. The syntax section below shows the fourth argument bracketed as optional. Separately, the first argument list names the second and third arguments as search column and result column, while the rest of the page calls them range and index. The page never reconciles either pair.

Neither inconsistency changes what the function does, and both change what a first-time reader believes it does. If you have ever written a VLOOKUP from memory and been surprised by the result, there is a reasonable chance you learned the function from whichever of the two descriptions you happened to read first.

It carries no date. There is no publication date, no last-updated stamp and no version anywhere on it. The same is true of Google's HLOOKUP page and its lookup performance page. That matters when two of those pages say different things, because there is no way to tell which one was written last.

Your competitors are already auditing their sites. Get a free SEO audit and find out what yours is missing.

What Do the VLOOKUP Errors Actually Mean?

#N/A means the value was not found. Google's wording is that if #N/A is returned, a value is not found. In exact-match mode that means the search key is not in the first column. In approximate-match mode there is a second cause that catches people out: if the search key is smaller than the smallest value in the first column, VLOOKUP returns #N/A, because there is no value at or below it to match.

#VALUE! usually means the index is wrong. Google marks this one as important: index only accepts a number. A column letter, a text label or a reference that resolves to text will not do. An index outside the range's column count lands here too.

A wrong answer with no error at all is the one to watch for. Two causes account for most of them. The first is whitespace, and Google is explicit that an empty string and a single space are not equivalent. The second is numbers or dates stored as text in the first column, which Google also tells you to avoid. Both produce a lookup that fails to match something a human reading the sheet would call identical.

Multiple matches return the first one, silently. Google states that where several search key values match, the value returned is the one whose associated search key is first matched in the search key column, and its troubleshooting section says flatly that VLOOKUP only returns the first match. There is no warning and no indication that a second match existed. If duplicate keys are possible in your data, VLOOKUP is the wrong tool for auditing them.

COUNTIF over the key column is the usual companion check here. Running it alongside the lookup tells you how many matches existed, which is the information VLOOKUP throws away, and it costs one extra column.

Multiple criteria are not supported. Google says VLOOKUP cannot be directly applied on multiple criteria, and the workaround it offers is a helper column that concatenates your two keys into one. That is the documented route, not a hack.

The helper column has a cost worth naming: it has to exist in both tables, built the same way in each, or the concatenated keys will not match. That is two more places for a stray space to break the lookup, which is why multi-criteria work often ends up in QUERY or in a pivot table instead.

Wildcards are supported, with one condition. A question mark matches any single character and an asterisk matches any sequence of characters, and Google states that to use wildcards in VLOOKUP you must use an exact match, meaning is_sorted set to FALSE.

VLOOKUP, HLOOKUP, XLOOKUP or INDEX and MATCH?

HLOOKUP is the same function rotated. Its syntax and argument order are identical, its is_sorted default is also TRUE, and it searches the first row of a range instead of the first column, returning a row index. Two differences matter. An out-of-bounds index returns #VALUE! rather than #N/A, and wildcards are not supported at all. Google's HLOOKUP page says search keys based on regular expressions or wildcard patterns are not supported and points you to QUERY instead. Two sibling functions, documented by the same team, give opposite answers on wildcards.

If you need pattern matching across a row rather than down a column, that constraint is the whole answer: transpose the data, or use QUERY as Google suggests. There is no wildcard wording that will make HLOOKUP behave like VLOOKUP here.

XLOOKUP barely appears in Google's VLOOKUP documentation. The only mention on the whole page is a tip suggesting XLOOKUP for more flexible database queries in BigQuery. That is a narrow recommendation about a different product, and the page draws no other comparison between the two functions. We are not going to extrapolate a general recommendation from it.

INDEX and MATCH is the pairing Google itself raises for flexibility. Its lookup performance page names VLOOKUP, HLOOKUP, MATCH and INDEX as the lookup functions, says VLOOKUP is slightly faster, and says INDEX and MATCH can provide more flexibility. It also suggests using MATCH once to find a position and reusing it, rather than running two VLOOKUPs over the same table.

The practical dividing line is direction. VLOOKUP cannot look to the left of its search column, and INDEX with MATCH can, because MATCH finds the position and INDEX reads from wherever you point it. If restructuring the source table is not an option, that is the reason to switch.

The second reason is brittleness. An index of 3 in a VLOOKUP is a column position, so inserting a column inside the range silently changes which field comes back. MATCH looks the column up by its header text, which means the same formula keeps returning the field you asked for after someone rearranges the table.

The performance question is smaller than the internet suggests for most sheets. Google's own performance page offers no numbers and no thresholds at all. Its actual advice is to remove duplicate values, avoid nesting IMPORTRANGE or SORT inside a lookup, sort once with sort range rather than repeatedly with the SORT function, and use IF to skip blank or error search keys before they reach the lookup. None of that is about choosing between the functions.

Taken together, that is an unusually clear signal. A page whose entire subject is lookup performance spends its advice on data hygiene rather than on function choice, which suggests the function you pick is rarely the bottleneck in a sheet that feels slow.

Most sites never find out why their best pages do not rank. Get a free SEO audit and see yours.

Final Thoughts

VLOOKUP is a four-argument function with one genuine trap in it, and the trap is the argument people leave off. Write FALSE every time, keep your lookup values in the left-hand column of the range, lock the range with dollar signs, and you have eliminated the three failures that account for most broken lookups.

The wider lesson from reading Google's documentation closely is that it is not a single consistent voice. Within one page the index bounds are stated two ways and the arguments carry two sets of names. Across three pages, wildcards are supported in one function and explicitly unsupported in its twin, and a performance recommendation appears on one page and is absent from the page dedicated to performance. None of the three pages is dated, so none of them can be treated as the later word.

That is not a reason to distrust Google's documentation, which remains the only authoritative description of what these functions do. It is a reason to treat any single sentence in it as one data point rather than as a settled rule, particularly where a second page covers the same ground.

Test the behavior you are about to depend on against your own data rather than against a sentence in a help article. If you are building something larger than a lookup column, our walkthrough of how to create a Google Sheets inventory template shows where lookups sit inside a sheet that has to keep working next month.

Want this done for you?

SEO Stuff gets businesses cited by ChatGPT, Gemini, Perplexity and Claude, and ranking in Google. Start with the free audit, a call, or the package.

See where you stand

A free, manual SEO + AI search audit of your site, with a report within 48 hours. No credit card, no sales call.

Get my free audit

Talk it through

A free 15-minute call with the founder about whether AI search is a meaningful opportunity for your business.

Free strategy call

Have it done

The Done-For-You Package: audit, 10 pages of content, 3 DR50+ placements, dashboard. $999 once, delivered in 21 business days.

See the package