How can we sort multiple values horizontally using VLOOKUP?
We send inspectors to stores once or maximum twice a month and we have a store scoring system.
Audit information is written on the home page, including the date on which the auditor went and where the auditor went.
Additionally, there is a page called scores, and on the score page, I can sort the scores and inspector names by store, using VLOOKUP, and the names of the inspectors by month in the column header.
HOMEPAGE
| DATE | STORE NAME | PERSON | AUDITPOINT | |
|---|---|---|---|---|
| 01.01.2024 | NEW YORK | JOHN DOE | JOHN@GMAIL.COM | 100 |
| 11.01.2024 | NEW YORK | JOHN DOE | JOHN@GMAIL.COM | 80 |
| 03.01.2024 | UTAH | JACK WILSON | JACK@GMAIL.COM | 90 |
| 04.01.2024 | NEVADA | JACK WILSON | JACK@GMAIL.COM | 90 |
| 12.01.2024 | NEVADA | JOHN DOE | JOHN@GMAIL.COM | 75 |
| 01.02.2025 | NEVADA | JOHN DOE | JOHN@GMAIL.COM | 75 |
The page where the formula should be.
POINT Search according to the current month of 01/01/2024, that is, audits between 01/01/2024` and 31/01/2024 D2 IS 01/01/2024
| STORE | 1. POINT | 1. PERSON | 2. POINT | 2. PERSON | AVERAGE |
|---|---|---|---|---|---|
| NEW YORK | 100 | JOHN DOE | 80 | JOHN DOE | 90 |
| UTAH | 90 | JACK WILSON | 90 | ||
| NEVADA | 90 | JACK WILSON | 75 | JOHN DOE | 82.5 |
Google Sheet Link : google sheet table
Solution
You may try:
=torow(sortn(filter({HOMEPAGE!$F:$F,HOMEPAGE!$D:$D},HOMEPAGE!$C:$C=$B10,eomonth(HOMEPAGE!$B:$B,)=eomonth(D$2,)),2,,1,))
- How to create a formula that will repeat a range by splitting one column and joining with the other column?
- Google Sheets Splitting Text to Rows and Duplicate Surrounding Rows Entries
- How to transpose & split multiple columns and repeat specific cells in a column
- How do you deal with multiple values in a single cell separated by comma?
- Copy specific fields to another sheet
- Find Text and copy another cells
- Generating date range based on allowed weekdays, repeat times and times of day in Google Sheets
- How to use a query within a query?
- Find first empty row and scroll to it, Lastrow not working due to formulas
- Map a table, replacing values based on other cell
- Sum value if matching value is not blank in range leaving it blank if empty
- A Script to Simplify Creating a SO Table
- Google Sheets API v4 and valueInputOption
- Get Google Sheet by ID?
- ReferenceError: C is not defined
- How to sum up multiple VLOOKUP results?
- Conditional Formatting in Google Sheets with another table INDIRECT
- How to reference a cell to the left of active cell in Google script
- How to show instruction with image after install google workspace extension?
- How to use SpreadsheetApp.Spreadsheet.getChanges method?
- Get the user that changed specific cell
- Daytime and Nightime Occurrence Duration of an Event
- Recombining 4 pairs of numbers
- GmailApp.sendEmail: is it possible to schedule send an email?
- Why do I get the error SPLIT parameter 1 empty error when it isn't?
- Is there a faster way to sort sheets with AppScripts?
- ISO-8601 String to Date in Google Sheets cell
- How can we sort multiple values horizontally using VLOOKUP?
- COUNTIFS WITHIN DATE RANGE GOOGLE SHEET
- conditional formatting on a single cell where criteria is another column [solved]