Forum Discussion
Solved
Help with formula (Index/lookup)
Hello all
I imagine this should be a fairly simple one for some of the geniuses here.
In the attachment I have a data array and need a formula which can convert it into a smaller table. Please be mindful of the difference in the 'Quarter' headers. The terms in the real world data I am using won't be a direct match to eachother, so the formula will have to match the cells which contain e.g. 'Quarter 1', rather than match it.
Hope this makes sense. Very grateful for help as always.
Best
S
=FILTER($C$2:$C$13,($A$2:$A$13=$F2)*ISNUMBER(SEARCH(G$1,$B$2:$B$13)))
3 Replies
As a variation, also using FILTER, this makes use of a "helper column" See the attached file
=FILTER($D$2:$D$13,($A$2:$A$13=$G2)*($C$2:$C$13=H$1))- Thank You both. This is very helpful!
=FILTER($C$2:$C$13,($A$2:$A$13=$F2)*ISNUMBER(SEARCH(G$1,$B$2:$B$13)))
