I am trying vlookup. I get #N/A when in FALSE. When set to default (or TRUE), I get 80% correct and 20% wrong data.


There are no duplicates in A:F - double checked.

Function is set on Calculations!K:K

File is here: https://docs.google.com/spreadsheets/d/1eTZwOlopINDPGcUNrmY6LtsJhnmMdTpwZbQ5mzb7krc/edit?usp=sharing

  • Welcome. Please add a brief description of your search/research efforts. Ref. How to Ask.
    – Rubén
    Oct 10 '18 at 18:33
  • Sorry, Question is properly edited, file link should be open Oct 13 '18 at 15:48

Placing the following array formula into Calculations!K1 will fill the column with a match where one is found or with the raw value from Calculations!C:C where no value is found on the Image! sheet:

=ArrayFormula(IFERROR(IF(ROW(C:C)=1;"Product";IF(C:C="";"";VLOOKUP(C:C;QUERY({QUERY(Image!B:G;"Select B, G Where Not B=''");QUERY(Image!C:G;"Select C, G Where Not C=''");QUERY(Image!D:G;"Select D, G Where Not D=''");QUERY(Image!E:G;"Select E, G Where Not E=''");QUERY(Image!F:G;"Select F, G Where Not F=''")});2;FALSE)));C:C))

Understand that array formulas take the space of the entire row, not just one cell. So if you use the array formula in K1, the entire column will be used by that formula. This means that you can not directly edit any cells in Calculations!K:K. If there are values you want to change, you will need to add the value of whatever is in Calculations!C:C for that row into the grid in the Image! sheet.

For instance, "Xiaomi Quick Charge Qi Wireless Charger" does not appear in the Image! sheet and so cell Calculations!K7 will just show "Xiaomi Quick Charge Qi Wireless Charger." If you want that to say something different, you'll need to add "Xiaomi Quick Charge Qi Wireless Charger" into a new row in the Image! sheet Column B with the alternate short name you want in Columns A and F of that new row.

An explanation of the formula:


The ArrayFormula wrapper makes this an array formula. An array formula returns a result for every row in a given range — in this case, every row within Column C (C:C) of the Calculations! sheet. Since this array formula will be placed in K1, it will return something in every row of Column K based on what it finds in each row of Column C (even if that result is NULL).


The IFERROR formula will catch where anything is not found in the Image! sheet. The default if that happens is found at the end of the formula — C:C — which just means "Copy over into this column whatever is in Column C for this row."


If the row number the ArrayFormula is assessing is Row 1, just put the word "Product" (i.e., the header for the column goes in Column K Row 1, or K1).


If the row number is NOT Row 1, then check to see whether the cell in Column C for the current row is empty. If it is, also put a NULL value in Column K for that row.


If the row is not row 1 and the value in Column C is not blank, move on and VLOOKUP the value in each remaining row of Column C. I'll get to the second part of this below, where the blank appears above, but we're going to form ONE long two-column search range there. The VLOOKUP will search for the value from Column C in that two-column range, searching the first column and returning whatever is in the second (2) column. The VLOOKUP ends with "FALSE" which means that the range we will be searching is not in order numerically or alphabetically.

QUERY(Image!B:G;"Select B, G Where Not B=''")

There are five similar QUERY calls placed one after the other and separated by colons (;). The colons tell us to stack these QUERY results on top of one another. These QUERY results won't be seen; they will just be held by Google Sheets as an invisible range to search in the VLOOKUP.

The first of the five inner QUERY formulas will pair Image!B:B beside Image!G:G, leaving out any pairs where Image!B:B doesn't contain a long name. This will give us a compact invisible list pairing [long product name | short product name ].

The other four QUERY calls are similar, and will continue stacking under the first QUERY results Image!C:C paired again with Image!G:G, Image!D:D paired with Image!G:G, Image!E:E paired with Image!G:G and Image!F:F paired with Image!G:G — again, all on top of each other so that, in the end, we have every possible result from Image!B:F in Column 1 of the invisible list and it's matching product name from Image!G:G beside it in Column 2 of that invisible list.


You'll notice an outer QUERY wrapper surrounds the inner five QUERY calls. This is just used to group each of those five separate arrays into ONE single array in the invisible world behind the scenes. We need it to be seen as one long QUERY instead of five stacked QUERYs, so that the VLOOKUP understands it.

  • Thank you for building the ARRAYFORMULA; this solution gets my vote. I can see how a single query needed to become a series of queries in order to satisfy the array conditions (Have I got it right?). And "if this is row 1" - I didn't see that coming! And you allowed for error checking! Since there are (and may continue to be) products that do NOT have a match, FWIW I would change the IFERROR value to something like "Not Listed". This would also enable the questioner to apply conditional formatting to Column K so that any error is highlighted and the questioner can take appropriate action.
    – Tedinoz
    Oct 16 '18 at 19:56
  • I appreciate the thoughtful reply, Tedinoz. Yes, in order to stack the five separate QUERY results, we needed to "create an array of the arrays." VLOOKUP requires that it look in "one range," so we Frankenstein one together using a QUERY of QUERYs. I agree that other IFERROR results could work; my thinking upon looking at the OP's sheet is that the results in Column K are actively going to be sent/used other places where end users may see them, which is why I opted for something user-friendly in that space.
    – Erik Tyler
    Oct 16 '18 at 22:18

This formula will will serve the purpose. Copy this into Cell K2 and copy down the column.

=query(Image!$A$1:$G,"select A where B='"&C2&"' OR  C='"&C2&"' OR  D='"&C2&"' OR  E='"&C2&"' OR  F='"&C2&"'",0)

There are a couple of issues which complicate this solution.
First - the value in C2 ("Clean Title 2") could be in any one of five columns in the "Images" sheet. So a conventional VLOOKUP is not going to suit.
Second - the information on the "Images" sheet is not sorted in any way.
Third - and this is only clear after some analysis, a match does not exist for every value of C2.

The questioner used this formula.


There are a few problems with this.
First, a colon, instead of a comma, precedes the Column Index.
Second, the lookup range commences with A and the Column Index is one, so this will always look for C2 in ColumnA - and no such match exists.
Third, the default [is sorted] parameter applies (=TRUE). In a scenario where the data is NOT sorted, VLOOKUP will simply return the the nearest match. But this isn't good enough. When the [is sorted] parameter is set to TRUE, every cell has a match; when [is sorted] is set to FALSE, there are NO matches.

This screenshot compares the results of VLOOKUP with [is sorted] set to True and to FALSE

VLOOKUP comparison

In the screenshot shown below, I've included a column showing the results of the QUERY, as well as the VLOOKUP result for each of the five columns in "Images".
There are a couple of things to note:
1 - There are four products (Highlighted in RED) where there is NO match for "Clean Title 2".
2 - The VLOOKUP results show that the "Clean Title 2" could be in any one of four columns. There are, as the questioner noted, no duplicates.
3 - The query results match the various VLOOKUP results exactly, which confirms that QUERY is a good way to find a match in this scenario.

Detailed VLOOKUP analysis

  • Perhaps someone brighter than I can show the questioner (and me) how to use/convert this formula to an arrayformula.
    – Tedinoz
    Oct 16 '18 at 9:00
  • Tedinoz, see below. Good catches and suggestion. You clearly put a lot of time into helping this poster.
    – Erik Tyler
    Oct 16 '18 at 12:14
  • 1
    @ErikTyler Heh, heh. It's more like an aversion to being proved wrong, or at least to satisfy myself that I've considered the possible outcomes. For example, in this case, the lack of a match for four products was unexpected.
    – Tedinoz
    Oct 16 '18 at 20:03
  • Yes, while I enjoy helping people out where I can, the nerd in me just likes the puzzle of it all; so I get where you're coming from in wanting to be sure to include all angles.
    – Erik Tyler
    Oct 16 '18 at 22:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.