# Google Sheets help **URL:** \ **Category:** Factual Questions\ **Created:** [August 12, 2022, 5:09pm UTC](https://boards.straightdope.com/t/google-sheets-help/969569 "2022-08-12T17:09:59Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [August 12, 2022, 5:09pm UTC](https://boards.straightdope.com/t/google-sheets-help/969569/1 "2022-08-12T17:09:59Z")
So I’ve put together a spreadsheet tracking one of my fantasy baseball leagues. (I know what you’re thinking, and the answer is yes, I’m very popular at parties.) So each week I pull in the rankings and underlying stats (or at least, I have since week 8). Today, I created a dynamic chart that changes based on the particular stat I want to look at. The problem is that I have no idea how it works. Let me try to recreate it with a little less noise: Raw data (all numbers are cumulative): Week | Team | Runs | HRs 1 | TeamA | 10 | 5 1 | TeamB | 8 | 6 2 | TeamA | 17 | 9 2 | TeamB | 14 | 10 In a separate tab, I have my dynamic chart. It has a drop down cell to select which specific stat I want to look at, and then this: Team | 1 | 2 TeamA | (formula) TeamB | (formula) The formula is populated across for all teams and all weeks. Here it is (please assume all $ are where they need to be, I’m typing this by hand): =filter(query(RawData!A:D, “SELECT “&SUBSTITUTE(ADDRESS(1, MATCH(, RawData!A1:D5, 0), 4), 1, “”)), RawData!A:A=B2, RawData!B:B=A2) The filter I understand. I want to pull the number of runs (“”) from Team A (“RawData!B:B”) in Week 1 (“RawData!A:A). The MATCH portion figures out which column in RawData the drop down is referring to. The QUERY/SELECT/SUBSTITUTE turns the match (when isolated, returns the letter of the column) into a column designation, but I don’t know HOW it does that. ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [August 12, 2022, 5:27pm UTC](https://boards.straightdope.com/t/google-sheets-help/969569/2 "2022-08-12T17:27:02Z")
Damn - that formula got messed up because I used pointy brackets and discourse didn’t like it. Here: =filter(query(RawData!A:D, “SELECT “&SUBSTITUTE(ADDRESS(1, MATCH(DropDown, RawData!A1:D5, 0), 4), 1, “”)), RawData!A:A=B2, RawData!B:B=A2) ---
**Author:** ![Ziberian](https://avatars.discourse-cdn.com/v4/letter/z/919ad9/32.png) [@Ziberian](https://boards.straightdope.com/u/Ziberian)\ **Post date:** [August 13, 2022, 3:31am UTC](https://boards.straightdope.com/t/google-sheets-help/969569/3 "2022-08-13T03:31:04Z")
I’m not sure what you mean by ‘column designation’, but I’m going to assume you mean A, B, C … The ADDRESS function returns a cell address in the form of a text. In this case that’s going to be row 1, & whatever column is returned by MATCH. You say MATCH returns the letter of the column, so let’s say ADDRESS returns C1. As I read your formula, SUBSTITUTE will then search cell C1 for a ‘1’, substitute a blank (“”) for that 1, leaving a ‘C’. Since you say the column letter was returned by MATCH, this would be a whole set of unnecessary steps so I’m probably misunderstanding the question. And it’s been a while since I programmed in Google, so I may be overlooking something. But I enjoyed trying to puzzle it out so I’ll post this anyway. 🙂 ---
**Author:** ![kenoMD](https://avatars.discourse-cdn.com/v4/letter/k/f9ae1b/32.png) [@kenoMD](https://boards.straightdope.com/u/kenoMD)\ **Post date:** [August 13, 2022, 4:42pm UTC](https://boards.straightdope.com/t/google-sheets-help/969569/4 "2022-08-13T16:42:49Z")
I don’t believe you have copied the formula correctly. MATCH function only looks at a single row or column, you have it searching in A1:D5. As written it should return an N/A The FILTER portion of your formula is superfluous. You are filtering the output of a QUERY function, the query function is capable of filtering, sorting, aggregating, etc. ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [August 13, 2022, 4:55pm UTC](https://boards.straightdope.com/t/google-sheets-help/969569/5 "2022-08-13T16:55:01Z")
> [@kenoMD](#): > > The FILTER portion of your formula is superfluous. I don’t think that’s right - if I take it out, the QUERY returns the entire column. FILTER narrows it down by team and week. > [@kenoMD](#): > > the query function is capable of filtering, sorting, aggregating, etc. That very well may be right - I just don’t know how to do it. ---
**Author:** ![kenoMD](https://avatars.discourse-cdn.com/v4/letter/k/f9ae1b/32.png) [@kenoMD](https://boards.straightdope.com/u/kenoMD)\ **Post date:** [August 13, 2022, 7:55pm UTC](https://boards.straightdope.com/t/google-sheets-help/969569/6 "2022-08-13T19:55:27Z")
Try these: > **[Query Language Reference (Version 0.7)  |  Charts  | ...](https://developers.google.com/chart/interactive/docs/querylanguage)** > **[Query Function in Google Sheets - Complete Tutorial | Coding is for Losers](https://codingisforlosers.com/google-sheets-query-function/)** > > Learn how lazy Google Sheets users take advantage of the QUERY function, which lets you write SQL queries in a Sheet. Sit back and watch the data flow. > **[Google Sheets Query: How to Use Order By - Statology](https://www.statology.org/google-sheets-query-order-by/)** > > This tutorial explains how to use the ORDER BY clause in Google Sheets query, including several examples. (the second and third are easiest to understand)