Google Sheets - Query Multiple Sheets That Have Different Structure

Question

Seeking assistance regarding how to structure a query that will be processing data from multiple sheets (ie tabs), however both sheets have different data structure.

The first query (below) queries a tab that contains all of my expenses itemised. This sums them by month. =query(Expense_Data, "SELECT C, SUM(Q) where T Matches 'Expense' GROUP BY C ORDER BY C desc limit 3 label SUM(Q) 'Expenses'",1)

Example Data Output Below

Date	Expenses
01/01/2021	-$1000
01/02/2021	-$1500
01/03/2021	-$1000

What I am seeking is to query another sheet which contains data (located in column G) that I wish to return based upon the date returned from the first query (located in column A), which I will then calculate the difference between. My issue is associating the 2 data sets together. Any support would be greatly appreciated!

Date	Expenses	Budget	Difference
01/01/2021	-$1000	-$2000	-$XXXX
01/02/2021	-$1500	-$1500	-$XXXX
01/03/2021	-$1000	-$1500	-$XXXX

share a copy/sample of your sheet with example of desired result — player0, Jun 05 '21 at 21:29
Can do. Here is a sample: https://docs.google.com/spreadsheets/d/1eJerVMig0CYzFgqmbZz1DB4LYwh5zXmtrdvTJEYc6ts/edit#gid=608657316 — raym01, Jun 06 '21 at 21:51

player0 · Accepted Answer · 2021-06-07T07:09:40.907

2

try:

=QUERY(Expense_Input, 
 "select C,sum(Q) 
  where T matches 'Expense' 
  group by C 
  order by C desc 
  limit 3 
  label sum(Q) 'Expenses', C'Month' 
  format C'mmmm yyyy'", 1)

then:

={"Budget"; ARRAYFORMULA(IFNA(VLOOKUP(TO_TEXT(A13:A), 
 {'Expense Lookup (Monthly)'!C:C&" "&'Expense Lookup (Monthly)'!D:D, 
 SUBSTITUTE('Expense Lookup (Monthly)'!G:G, "$", )*1}, 2, 0)))}

and:

={"Difference"; INDEX(IF(A13:A="",,C13:C-B13:B))}

update

in one go:

=ARRAYFORMULA(QUERY({QUERY(Expense_Input, 
 "select C,sum(Q) 
  where T matches 'Expense' 
  group by C 
  order by C desc 
  limit 3 
  format C 'mmmm yyyy'", 1), IFNA(VLOOKUP(TEXT(INDEX(QUERY(Expense_Input, 
 "select C,sum(Q) 
  where T matches 'Expense' 
  group by C 
  order by C desc 
  limit 3",1),,1), "mmmm yyyy"), 
 {'Expense Lookup (Monthly)'!C:C&" "&'Expense Lookup (Monthly)'!D:D, 
 SUBSTITUTE('Expense Lookup (Monthly)'!G:G, "$", )*1}, 2, 0))}, 
 "select Col1,Col2,Col3,Col3-Col2 
  label Col1'Month',Col2'Expenses',Col3'Budget',Col3-Col2'Difference'"))

edited Jun 07 '21 at 07:09

answered Jun 06 '21 at 22:11

player0

124,011
12
67
124

Thanks. Is there a way that this can be achieved within the same query? As I thought we could achieve the 'Difference' via the arthimetic function ie (Col2-Col3)? – raym01 Jun 07 '21 at 00:43
1

Thankyou it appears to work. I'm curious to understand how this vlookup/query combination works. Are there any references you can share with me to understand it in more detail? :) Thanks again – raym01 Jun 08 '21 at 20:51
@raym01 vlookup looks up for match in first column of given range and it can return any column from given range. in your case we create virtual table/array with array brackets {} and we put there next to each other these formulae: we run the first query within one sheet. next to it we run the same query but we use only first column of the outputed query table as the basis for looking up things in the second sheet and thats basically it – player0 Jun 08 '21 at 21:43

Google Sheets - Query Multiple Sheets That Have Different Structure

1 Answers1

update