How to use advanced lookup for multiple criteria across multiple rows in Google Sheets?

Other Questions Google Sheets
1

I’m working on a project where I need to match dates, times, and metric categories across multiple rows in my Google Sheets data. Every day, I input new metrics in a specific format, with rows showing data at different time intervals, separated by date.

I want to create a dashboard that automatically displays the current metrics without manual searching through the data tables. The goal is to have the metrics update when the next interval changes.

I know I can use =SPLIT(NOW()) for the date and time later, but for now, I’m trying to make it work with static intervals and dates. I’ve attempted using Index Match with AND() and other functions, but I keep running into errors. I also tried an array formula, but that didn’t work either.

Here’s a simplified example of what I’m trying to achieve:

| Date       | Time  | Category | Metric |
|------------|-------|----------|--------|
| 2023-05-01 | 09:00 | Sales    | 100    |
| 2023-05-01 | 12:00 | Sales    | 150    |
| 2023-05-01 | 15:00 | Sales    | 200    |
| 2023-05-01 | 09:00 | Support  | 50     |
| 2023-05-01 | 12:00 | Support  | 75     |
| 2023-05-01 | 15:00 | Support  | 100    |

How can I create a formula that looks up the correct metric based on multiple criteria (date, time, and category) across these multiple rows? Any help would be appreciated!

2

I’ve faced similar challenges with Google Sheets, and I can share what worked for me. The QUERY function is your best bet here. It’s powerful and flexible for handling multiple criteria across rows.

Here’s a formula that should work for your situation:

=QUERY(A1:D, “SELECT D WHERE A = date '” & TEXT(C1, “yyyy-mm-dd”) & “’ AND B = '” & TEXT(C2, “HH:mm”) & “’ AND C = '” & C3 & “'”, 0)

Where C1 is your date cell, C2 is your time cell, and C3 is your category cell.

This formula allows you to input the date, time, and category you’re looking for, and it’ll return the corresponding metric. You can then use this in your dashboard and it’ll update automatically as you add new data.

Remember to adjust the range (A1:D) based on your actual data layout. Also, ensure your date and time formats in the sheet match the format in the formula.

Hope this helps! Let me know if you need any clarification.

3

hey john, have you tried using FILTER? it’s pretty handy for this kinda stuff. something like:

=FILTER(D:D, A:A=DATE(2023,5,1), B:B=TIME(9,0,0), C:C=“Sales”)

just replace the date/time/category with cell references. it’ll give you the metric you want. lemme know if that works!

4

For your scenario, I’d recommend using the VLOOKUP function combined with MATCH. Here’s a formula that should work:

=VLOOKUP(A1&B1&C1, {ARRAYFORMULA(A:A&B:B&C:C), D:D}, 2, FALSE)

A1 should contain your date, B1 your time, and C1 your category. This formula creates a unique key by concatenating these three criteria, then looks it up in a similarly constructed array.

Make sure your date and time formats in A1 and B1 match exactly with your data. You might need to use the TEXT function to format them correctly.

This approach is efficient and should update automatically as you add new data. It’s also easy to modify if you need to change your criteria or add more columns later.