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

3 Replies
best response confirmed by reevesgetsaround (Brass Contributor)
Solution

# Re: Help with formula (Index/lookup)

``=FILTER(\$C\$2:\$C\$13,(\$A\$2:\$A\$13=\$F2)*ISNUMBER(SEARCH(G\$1,\$B\$2:\$B\$13)))``

# Re: Help with formula (Index/lookup)

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))`` # Re: Help with formula (Index/lookup)

Thank You both. This is very helpful!