Pull data from row if a cell contains a defined value

0
votes

I'm currently working on a report and I'm attempting to pull in select values from a row to another spreadsheet. The twist is that I'd like to pull in that information in descending order only if a cell on that row contains a certain value.

For this report, I'm interested in pulling in data from column A, B, C, and E, only if the cell in column D is a "Yes". The data must also be listed in descending order, so essentially having a list of the top 10 people who have had the most change in ranking and are located in the Chicago office.

The formula I'm using pulls in data from that spreadsheet into another sheet for reporting purposes:

=Query('Sales'!A2:E,"Select A,B,C,E where A<>'' Order by D Desc Limit 10")

However, it pulls in the data for all columns on the sheet, regardless of whether they're in the Chicago office or not. Is there a way to single out the top 10 rows that contain the "Yes" in column S and get data for the cells in column A, B, C, E in those rows to pull in descending order into another sheet?

Link to sample sheet: https://docs.google.com/spreadsheets/d/1psaOkE2oFMsKlXSawot1zujq5HX9ziowf95cJR_O7rA/edit?usp=sharing

1
google-sheetsgoogle-sheets-formula
That is not an Excel formula. Why reference Excel? – Scott Craner
if you provide a sample sheet it would be helpful – MattKing
Sorry about that, I've made a sample sheet here with some data: docs.google.com/spreadsheets/d/… – cade6

1 Answers

1
votes

You probably want something like this:

=Query('Sales'!A2:U,"Select A,C,K,T where A<>'' AND S='Yes' Order by U Desc Limit 10")