Forum Discussion
Match data in grid from 3 variables
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.
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?
Epic! That was the issue. Thank you so much for your help!
I'm rather surprised that using it only 20 times would have much effect but here is a variation you could try instead:
=FILTER( FILTER( $C$2:$H$100, $C$1:$H$1= in_Var3), (in_Var1 = $A$2:$A$100 ) * (in_Var2= $B$2:$B$100 ) )