CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulatransposearray-formulasgoogle-sheets-query
IMTheNachoMan
IMTheNachoMan

Reputation: 5811

use ARRAYFORMULA to find all matching rows/values in a source table from a lookup table

I have a source table like so. As you can see, each each Lookup Value can have one or more Result.

| Lookup Value | Result |
|--------------|--------|
| a            | a1     |
| a            | a2     |
| a            | a3     |
| b            | b1     |
| b            | b2     |
| c            | c1     |
| c            | c2     |

Then I have an input table like so:

| queries | results |         |         |         |         |         |
|---------|---------|---------|---------|---------|---------|---------|
| a       | ...     | ...     | ...     | ...     | ...     | ...     |
| c       | ...     | ...     | ...     | ...     | ...     | ...     |

The ... for each row should be the transposed values from the lookup table. So, for example, the above table would look like this:

| queries | results |    |    |
|---------|---------|----|----|
| a       | a1      | a2 | a3 |
| c       | c1      | c2 |    |

Right now I have to use multiple formulas like so:

enter image description here

I am trying to replace it with a single ARRAYFORMULA but it doesn't seem to work.

enter image description here

Is there another way to do this? Basically lookup all the matching rows from a lookup table and then transpose them?

Upvotes: 0

Views: 367

Answers (2)

Erik Tyler
Erik Tyler

Reputation: 9345

Suppose your "Lookup Value" and "Result" data run from A1:B (with headers in A1 and B1). And suppose that your "queries" list is in D1:D (header in D1) with the "results" header in E1.

Depending on the maximum number of possible matches in B:B for any value in A:A, you could use this in E2:

=ArrayFormula(IFERROR(VLOOKUP(D2:D,QUERY(FILTER({A2:B,COUNTIFS(A2:A,A2:A,ROW(A2:A),"<="&ROW(A2:A))},A2:A<>""),"Select Col1, MAX(Col2) Group By Col1 Pivot Col3"),SEQUENCE(1,10,2),0)))

If your maximum possible matches is fewer than 10 or more than 10, feel free to edit the second argument of the SEQUENCE function accordingly.

Understand that, with such an array formula that is asked to process a range, you wouldn't be able to put other data anywhere below or to the right of your "queries and results" that you've asked the array formula to assess or fill. So if you want data under it, you'll need to limit your VLOOKUP from D2:D to, say, D2:D50 (or whatever your max queries range would be). Likewise, if that second argument of the SEQUENCE function is 10, you'll have "reserved" 10 columns (i.e., E:N) for possible results, and you won't be able to put data there or you'll "break" the array formula. That being the case, you may want to give yourself some sort of visual line of demarcation around the area you've reserved for the formula's use (e.g., change the background color of the block or place a border around it, etc.).

Upvotes: 1

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(IFERROR(VLOOKUP(D:D, SPLIT(TRANSPOSE(QUERY(TRANSPOSE(IF(ISNUMBER(
 QUERY(QUERY(A:B, "select A,count(A) where A is not null group by A pivot B"), "offset 1", 0)), 
 QUERY(QUERY(A:B, "select A,count(A) where A is not null group by A pivot B"), "limit  0", 1), 
 QUERY(QUERY(A:B, "select A,count(A) where A is not null group by A pivot B"), "offset 1", 0)))
 ,,999^99)), " "), TRANSPOSE(ROW(INDIRECT("A2:A"&COUNTUNIQUE(B:B)))), 0)))

0

Upvotes: 0

Related Questions