Hello,
I am new to excel and still learning INDEX & MATCH functions, your help here is greatly appreciated.
I have a data set which can have large number of records (several 1000s). The data is unsorted and contains date and few other fields. See attached sample data excel.
The challenge I have are:
1. Need to find specific values in the 'Value' column that are with in the date range given at cell F3 and G3
2. I also need to map these values to specific dates in 'Date' column
3. Once these values are obtained, I need to plot them on a scatter plot
For #1 above, I am trying to use INDEX and MATCH functions. I have figured out a way to create an array with Row numbers for valid entries (i.e. rows that meet the Date criterion specified above).
{=(Table1[Boolean]=TRUE)*(Table1[Date]>=F3)*(Table1[Date]<=G3)*MATCH(ROW(Table1[Boolean]), ROW(Table1[Boolean]))} (entered with Ctrl+Shift+Enter) See cell E7
This array formula does create an array of values with '0' (Zero) for rows that do not meet the criterion and actual row number for those that do meet the criterion. Example:
{1;2;3;4;5;6;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;95;96;97;0;99;100;101;102;...} <Trimmed rest of the array>
I am lost on how to get the actual data from 'Value' and 'Date' columns. I tried using INDEX but that's not returning an array (I only get the first value). For example:
{=INDEX(Table1[Value], (Table1[Boolean]=TRUE)*(Table1[Date]>=F3)*(Table1[Date]<=G3)*MATCH(ROW(Table1[Boolean]), ROW(Table1[Boolean])))} (entered with Ctrl+Shift+Enter) results in 104 (a scalar value, not an array). This should return an array, but it works sometimes and not others...See cell E8
Could you please help me define a formula that can:
1. Return only the valid row numbers (without the zeros) i.e. return {1;2;3;4;5;6;95;96;97;0;99;100;101;102;...}
2. Return the 'Value' and 'Date' fields corresponding to those rows
3. Is there a way to sort these values based on date?
Thank you so much!
-vatsy
I am new to excel and still learning INDEX & MATCH functions, your help here is greatly appreciated.
I have a data set which can have large number of records (several 1000s). The data is unsorted and contains date and few other fields. See attached sample data excel.
The challenge I have are:
1. Need to find specific values in the 'Value' column that are with in the date range given at cell F3 and G3
2. I also need to map these values to specific dates in 'Date' column
3. Once these values are obtained, I need to plot them on a scatter plot
For #1 above, I am trying to use INDEX and MATCH functions. I have figured out a way to create an array with Row numbers for valid entries (i.e. rows that meet the Date criterion specified above).
{=(Table1[Boolean]=TRUE)*(Table1[Date]>=F3)*(Table1[Date]<=G3)*MATCH(ROW(Table1[Boolean]), ROW(Table1[Boolean]))} (entered with Ctrl+Shift+Enter) See cell E7
This array formula does create an array of values with '0' (Zero) for rows that do not meet the criterion and actual row number for those that do meet the criterion. Example:
{1;2;3;4;5;6;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;95;96;97;0;99;100;101;102;...} <Trimmed rest of the array>
I am lost on how to get the actual data from 'Value' and 'Date' columns. I tried using INDEX but that's not returning an array (I only get the first value). For example:
{=INDEX(Table1[Value], (Table1[Boolean]=TRUE)*(Table1[Date]>=F3)*(Table1[Date]<=G3)*MATCH(ROW(Table1[Boolean]), ROW(Table1[Boolean])))} (entered with Ctrl+Shift+Enter) results in 104 (a scalar value, not an array). This should return an array, but it works sometimes and not others...See cell E8
Could you please help me define a formula that can:
1. Return only the valid row numbers (without the zeros) i.e. return {1;2;3;4;5;6;95;96;97;0;99;100;101;102;...}
2. Return the 'Value' and 'Date' fields corresponding to those rows
3. Is there a way to sort these values based on date?
Thank you so much!
-vatsy