Log In
Register
Home
Active Member
January 14, 2019
Offline
I am currently creating a breakout dashboard based on a few metrics that aggregate into a main dash where the metrics update dynamically based on my data validation drop down. There was one number where I needed to divide numbers based on multiple criteria similar to a sumIFS statement but I realized there is no formula that does this seamlessly. As a way around this I divided all the numbers in one column then created a formula to return that number based on two criteria using an INDEX match formula, only problem is that the data is on a different tab. I did end up getting my desired result but I am not sure why it worked. When I put the 1 in front of the match formula it makes it dynamic returning the result I want based on name and date but when I take the 1 out it only gives me the first row. No where does any match formula say by adding the 1 it makes it dynamic. I have deducted that is what the formula is doing but I was wondering if I could get more clarity if I am correct in this line of thinking. Please see below for full formula
=ArrayFormula(index(A2:J,MATCH(1,(B2:B='Dashboard '!$A$1)*(A2:A='Dashboard '!$B$1),0),6)
* Right after the match(1 is the one that is question when I take it out the forumla does not update and when I put in 1 it does. I know this has nothing to do with the exact match because those belong at the end of the forumla. When I click on the 1 it highlights search key "The value to search for. For example, 42, "Cats", or I24." But this can not be the case because the following part of the formula is technically my search key (B2:B='Dashboard '!$A$1)*(A2:A='Dashboard '!$B$1)
VIP
Trusted Members
December 7, 2016
Offline
Hello Jay,
I think you will find the answer in this blog post.
https://www.myonlinetraininghu.....in-a-range
Active Member
January 14, 2019
Offline
Hi so not quite but I believe we are on the right track of using a dynamic index match based on multiple conditions in my case name and date. I have a data validation of week and sales rep and I want to return a value when both conditions are met based on a huge list of source data. Like stated above this formula does the trick =ArrayFormula(index(A2:J,MATCH(1,(B2:B='Dashboard '!$A$1)*(A2:A='Dashboard '!$B$1),0),6) . BUT only if I add the one after MATCH(1, if I take the one out it is static. From the article you gave me it used True but only gives the first or last value. I would want mine to be truly dynamic. AKA return this value any time this week and this name is selected on my drop down data validation list. Example John Smith week of 11/21/2018 return his sales and then if I change the week or the name it will then find that cell connected to that name and date and return it. My question is why does this 1 make it dynamic no where in that article or the formula does it say add that one to make it update based on my two conditions week and name. If you could expand on this I would greatly appreciate it
Trusted Members
Moderators
November 1, 2018
Offline
First off, it looks like you're using Google sheets, not Excel?
The 1 doesn't make the formula dynamic, it's the value that is being looked up. This part of the formula:
(B2:B='Dashboard '!$A$1)*(A2:A='Dashboard '!$B$1)
produces an array of 1 and 0 values (1 where both conditions are met, 0 if not), so your working formula finds the first matching value. You could also use a FILTER formula in Sheets.
Active Member
January 14, 2019
Offline
Awesome that I think answers my question I wanted it to meet two conditions NAME and DATE which is on my breakout dash and is subject to change based on the data validation drop down list. EXAMPLE John Smith and week ok 12/01/2018 Return average sales for this week from this cell when both conditions are met. So I THINK what you are saying is by having the 1 there it needs both conditions to be be true and if it is 0 only one? I very much understand the part of the formula (B2:B='Dashboard '!$A$1)*(A2:A='Dashboard '!$B$1) It was just no where under any learning material did it have that distinction. Thank you very much I will go forward assuming this is true. Next I will have a look up for the company as a whole so I only need one condition to be true if what you say is correct the same formula should work with a 0 not a 1 this time.
Again thank you for the clarification and if there is an either way to index match based on multiple criteria that is subject to change (I.E) choosing name and date from a drop down list and aggregating an average based on those criteria to a dashboard please let me know!!
1 Guest(s)
