# ARRAYFORMULA, VLOOKUP, QUERY and AND together

**URL:** <https://community.glideapps.com/t/arrayformula-vlookup-query-and-and-together/62057>\
**Category:** Ask for Help\
**Created:** [May 23, 2023, 5:58pm UTC](https://community.glideapps.com/t/arrayformula-vlookup-query-and-and-together/62057 "2023-05-23T17:58:43Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![Ryan](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/ryan/32/15941_2.png) [@Ryan](https://community.glideapps.com/u/Ryan)\
**Post date:** [May 23, 2023, 5:58pm UTC](https://community.glideapps.com/t/arrayformula-vlookup-query-and-and-together/62057/1 "2023-05-23T17:58:43Z")

</div>

Hi guys,  
I am looking for your Google Sheets expertise.

I’m trying to combine ARRAYFORMULA, VLOOKUP, QUERY together.

If I take off the “AND Col1 \<= DATE '”&TEXT(F2:F,“yyyy-mm-dd”)&" " part it works perfectly, but if I do want to include it, the first row is the only row that works as expected.

=ARRAYFORMULA(VLOOKUP(  
A2:A,  
QUERY(  
{‘Income’!A2:A&‘Income’!C2:C,‘Income’!B2:B},  
“SELECT Col1, SUM(Col2) WHERE Col1 \>= DATE '”&TEXT(E2:E,“yyyy-mm-dd”)&“’ AND Col1 \<= DATE '”&TEXT(F2:F,“yyyy-mm-dd”)&“’ GROUP BY Col1”,  
0  
),  
2,  
0  
))

Happy to hear your thoughts.

---

<div class="post-metadata">

**Author:** ![gvalero](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/gvalero/32/1037_2.png) [@gvalero](https://community.glideapps.com/u/gvalero)\
**Post date:** [May 23, 2023, 6:28pm UTC](https://community.glideapps.com/t/arrayformula-vlookup-query-and-and-together/62057/2 "2023-05-23T18:28:54Z")

</div>

Hola,

The Arrayformula(Query(…)) combination is not supported in GS, never will work so you have to think differently about the solution.

If you give us an example with some images associated to what you want to get, we surely might find a workaround.

Saludos!

---

<div class="post-metadata">

**Author:** ![Ryan](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/ryan/32/15941_2.png) [@Ryan](https://community.glideapps.com/u/Ryan)\
**Post date:** [May 23, 2023, 7:08pm UTC](https://community.glideapps.com/t/arrayformula-vlookup-query-and-and-together/62057/3 "2023-05-23T19:08:05Z")

</div>

Thanks my friend,  
The thing is, it does work if I remove the AND part from my WHERE statement…

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [May 23, 2023, 7:11pm UTC](https://community.glideapps.com/t/arrayformula-vlookup-query-and-and-together/62057/4 "2023-05-23T19:11:45Z")

</div>

In arrayformula, you can’t use AND logic… use \* to multiply values… if the result is not 0… then it is a match…  
also… arrayformula will not work with filters (it will only process the first value)… as @gvalero said, you have to create workaround using vlookups and if formulas

---

<div class="post-metadata">

**Author:** ![gvalero](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/gvalero/32/1037_2.png) [@gvalero](https://community.glideapps.com/u/gvalero)\
**Post date:** [May 23, 2023, 7:13pm UTC](https://community.glideapps.com/t/arrayformula-vlookup-query-and-and-together/62057/5 "2023-05-23T19:13:55Z")

</div>

I don’t know, maybe knowing better what you try to do we can modify or create a better Query statement and remove the VLOOKUP() from your formula.

Bye

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [May 23, 2023, 11:26pm UTC](https://community.glideapps.com/t/arrayformula-vlookup-query-and-and-together/62057/6 "2023-05-23T23:26:05Z")

</div>

Can you provide a sample sheet with dummy data, and tell me what’s your expected result so I can dive in and try?

Also, have you considered moving this logic to a Glide calculated column, or do you need to see it in the Sheets?

---

<div class="post-metadata">

**Author:** ![Robert\_Petitto](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/robert_petitto/32/25193_2.png) [@Robert\_Petitto](https://community.glideapps.com/u/Robert_Petitto)\
**Post date:** [May 24, 2023, 3:02am UTC](https://community.glideapps.com/t/arrayformula-vlookup-query-and-and-together/62057/7 "2023-05-24T03:02:40Z")

</div>

> [@ThinhDinh](#):
>
> Also, have you considered moving this logic to a Glide calculated column, or do you need to see it in the Sheets?

This ☝

Maybe this will help?

> [@litter\_in\_bin\_sign Trash your Excel Formulas](https://community.glideapps.com/t/trash-your-excel-formulas/55788):
>
> wave Hey Gliders! By request, I created a video that explains how to replace 14 commonly used Google Sheet/Excel Formulas using Glide computed columns and other native Glide functionality. Formulas are timestamped in the description. For good measure, I also threw in how to use the Excel Formula plugin column upside_down_facepopcorn Enjoy! [[Glide: Replace 14 Excel Formulas with Glide Computed Columns] ](https://www.youtube.com/watch?v=YITKadLUKNE)

---

<div class="post-metadata">

**Author:** ![Ryan](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/ryan/32/15941_2.png) [@Ryan](https://community.glideapps.com/u/Ryan)\
**Post date:** [May 24, 2023, 5:27am UTC](https://community.glideapps.com/t/arrayformula-vlookup-query-and-and-together/62057/8 "2023-05-24T05:27:22Z")

</div>

Thanks guys!

Calculating this over Glide is quite straight forward, but I do need it in excel for cross reference etc. 🤐

A bit about what I’m trying to do:  
Table A is a log of all my incomes: Row ID | Category ID | Value | Transaction date

Table B is a smart summary of the transaction sum, grouped by the category ID and transaction period:  
Row ID | Category ID | Expected income | From date | To date

In table B, the same category can point to different periods (From → To dates), and the matching incomes from table A (both Category ID and date period) should be aggregated.

Hope this was clear enough

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [May 24, 2023, 11:42pm UTC](https://community.glideapps.com/t/arrayformula-vlookup-query-and-and-together/62057/9 "2023-05-24T23:42:18Z")

</div>

So you log all your incomes into table A, and table B is where you want to combine those logs into certain periods grouped by category? Is “Expected income” the column where the formula should be used?

---

<div class="post-metadata">

**Author:** ![Ryan](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/ryan/32/15941_2.png) [@Ryan](https://community.glideapps.com/u/Ryan)\
**Post date:** [May 25, 2023, 4:28am UTC](https://community.glideapps.com/t/arrayformula-vlookup-query-and-and-together/62057/10 "2023-05-25T04:28:26Z")

</div>

I log all my incomes into Table A, and Table B is where I combine those logs into certain periods grouped by category IDs.

Expected income is another numeric value (not calculated) that I add/ subtract from the grouped value as I tried to calculate it.

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [May 26, 2023, 1:30am UTC](https://community.glideapps.com/t/arrayformula-vlookup-query-and-and-together/62057/11 "2023-05-26T01:30:55Z")

</div>

> **[Glide Community - Table Query](https://docs.google.com/spreadsheets/d/1uLbeFBjSTcTFmtAUFbDMiNpnbkU83zhN_6VJCzvNbrU/edit#gid=2007058965)**
>
> Table A
> 
> Category ID,Value,Transaction Date
> Cat1,2000,May 1, 2023
> Cat1,1000,May 5, 2023
> Cat2,500,May 6, 2023
> Cat2,1500,May 8, 2023
> Cat2,2000,May 9, 2023
> Cat1,3000,May 10, 2023

Please check this and let me know if it works.

Using BYROW, which I always use instead of arrayformula now.

---

<div class="post-metadata">

**Author:** ![Ryan](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/ryan/32/15941_2.png) [@Ryan](https://community.glideapps.com/u/Ryan)\
**Post date:** [May 28, 2023, 5:12pm UTC](https://community.glideapps.com/t/arrayformula-vlookup-query-and-and-together/62057/12 "2023-05-28T17:12:50Z")

</div>

OMG,  
IT WORKED! 🙂

Thank you so much

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [May 28, 2023, 11:04pm UTC](https://community.glideapps.com/t/arrayformula-vlookup-query-and-and-together/62057/13 "2023-05-28T23:04:14Z")

</div>

Great to hear!

---

<div class="post-metadata">

**Author:** ![system](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/system/32/53398_2.png) [@system](https://community.glideapps.com/u/system)\
**Post date:** [May 29, 2023, 11:04pm UTC](https://community.glideapps.com/t/arrayformula-vlookup-query-and-and-together/62057/14 "2023-05-29T23:04:52Z")

</div>

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.