Search code examples
google-sheetsarray-formulasgoogle-sheets-formulagoogle-sheets-querygs-vlookup

Google Sheets query doesnt work if ID is less than the previous one

I extract data from this tab:

enter image description here

And then I import columns J, K, L into this second tab, based on the ID (HU-whatever)

enter image description here

So basically the query takes the HU-whatever on the second tab and searches for it in the first tab. If there is a match, returns columns J, K, L of that row:

=QUERY(ListaDeProducto!B6:Q82;"SELECT J,K,L WHERE B matches '"&B4&"'")

Unfortunately, when I dragged down to apply the query to all the HU-whatever in the second tab, I noticed that if the HU is smaller than the previous HU, the query just dies and returns no data at all.

Why is this happening? can it be solved? is it a bug or...?

Solution

  • use:

    =QUERY(ListaDeProducto!B6:Q82; "select J,K,L where B matches '"&B4&"'", 0)

    or:

    =QUERY(ListaDeProducto!B6:Q82; "select J,K,L where B matches '"&B4&"' limit 1", 0)

    or for the whole column/array:

    =ARRAYFORMULA(IFERROR(VLOOKUP(B4:B, 
 {ListaDeProducto!B6:B82, ListaDeProducto!J6:J82, 
  ListaDeProducto!K6:K82, ListaDeProducto!L6:L82}, 
 {2, 3, 4}, 0)))