Forum Discussion
Solved
Match data in grid from 3 variables
I'm having a hard time writing a formula to accomplish what I'm looking to accomplish. I have a grid of data that is somewhat odd - it's how a company has been managing their process for a long time and i can't really change it. I'm looking to extract data automatically to better format it for reports. The data is structured like this:
| Variable 3 | Variable 3 | Variable 3 | Variable 3 | Variable 3 | Variable 3 | ||
| Variable 1 | Variable 2 | x | x | x | x | x | x |
| Variable 1 | Variable 2 | x | x | x | x | x | x |
| Variable 1 | Variable 2 | x | x | x | x | x | x |
| Variable 1 | Variable 2 | x | x | x | x | x | x |
| Variable 1 | Variable 2 | x | x | x | x | x | x |
I need to be able to search the grid to pull the data based on all three variables. Does anyone have any thoughts on this?
Thanks!!
Angus239
hello,
Assuming that your Start(A2) and End(B2) has included a dateyou can use these following formulas to achieve your goal:
Total Hours
=ABS(24*((MIN(TIME(6,0,0),MOD(B2-TIME(18,0,0),1))-MIN(TIME(6,0,0),MOD(A2-TIME(18,0,0),1)))+(MAX(TIME(6,0,0),MOD(B2-TIME(18,0,0),1))-MAX(TIME(6,0,0),MOD(A2-TIME(18,0,0),1)))))Day Shift Hours
=IF(B2>=$J$2,HOUR(B2)-6+MINUTE(B2)/60,0)Night Shift Hours
=F2-D2OVERVIEW:
I've attached a sample file for you
6 Replies
As with so much in excel there are many possibilities and depend on a few factors. If we assume there will be 1 an only 1 possible combination of Variable 1, 2, and 3 then try something like:
=CHOOSECOLS( XLOOKUP( in_var1 & "@" & in_var2, $A$2:$A$100 & "@" & $B$2:$B$100, $C$2:$H$100), XMATCH( in_var3, $C$1:$H$1) )if you just need to "search" then an alternative maybe to just turn FILTERS on and use them to filter by Var 1 and Var 2.
So highlight the whole table and go to DATA and click Filters (or click CTRL-SHIFT-L):
Then you click the drop down arrows and enter in the search which values you want:
In the example on the left I typed "name1" and notice the list drops out all the names from 2-9 and if you further select the specific "name" it will only show rows with that specific name.
Then repeat on the second column.
Thank you very much for the fast reply. There is only one possible combination of all three variables and i need to stage the data in another location, so the formula you're presenting is ideal here. Unfortunately, I can't seem to get the formula to function.
I'm assuming in_var1 can just be replaced with a field that has the desired variable response? Not sure what else I could be missing here.
I had a typo, accidentally dropped the 'S' off of CHOOSECOLS
Fixed it up above.
If that isn't the problem are you useing Excel 365 or some old version? If you type =CHOOSEC do you see a tooltip / drop down that has CHOOSECOLS in it?
