Index match using IFS function

[Howlin]

Hi,

I want to look up values only where I match a certain criteria.

There is two input process on our line one with RW that applies no gsm to the product and one with FR that applies a gsm to the product. The only interesting data that I want is the FR data so that I can trend it.

The only way I can think of doing this is by using CONCATENATE function and combining the data in Cell A2:A20 with B2:B20. Then getting searching for example FR 1, but this feels not a neat solution

I've put the values I want in the column M and the values I get by it in G.

I think I could do an if or ifs formula for it?

Cheers,

Tom

[Khalidngo]

Hi Tom

Try this in M3:

=SUMIFS($C$2:$C$28,$B$2:$B$28,L3,$A$2:$A$28,K3)

Copy down...

Blessing

[AliGW]

Try this:

=SUMIFS($C$2:$C$28,$A$2:$A$28,K3,$B$2:$B$28,L3)

[Howlin]

That seems to work perfectly!

[Khalidngo]

and for FR in column L, try this {array formula} in L3 and copy down:

=IFERROR(INDEX($B$2:$B$28,SMALL(IF($B$2:$B$28="FR",ROW($B$2:$B$28)-1),ROW(A1))),"")
Enter with Ctrl+Shift+Enter (not just enter)

Regards,

[AliGW]

Thanks for the reputation!