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
– player0, Commented Jun 5, 2021 at 21:29
Can do. Here is a sample: docs.google.com/spreadsheets/d/… — raym01
– raym01, Commented Jun 6, 2021 at 21:51

player0 · Accepted Answer · 2021-06-07 07:09:40Z

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 7, 2021 at 7:09

answered Jun 6, 2021 at 22:11

player0

131k14 gold badges91 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

raym01 Over a year ago

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 Over a year ago

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

player0 Over a year ago

@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

Collectives™ on Stack Overflow

Google Sheets - Query Multiple Sheets That Have Different Structure

1 Answer 1

update

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

update

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related