Thread: SumProduct, Index & Left

I am using the formula below but want to extend its functionality.

Enter as array formula in V48:AJ61
= IF(SUMPRODUCT(--($R$7:$R$22=V$47))>=ROWS(AB$48:AB48),INDEX($AC$7:$AC$22,SMALL(IF($R$7:$R$22=AB$47,ROW($R$7:$R$22)-ROW($R$7)+1),ROWS(AB$48:AB48))),"")

This is fine where I can match entries in R7:R22 with the specific text in AB47 (their cost being in AC7:AC22).

But I want to pick up various general entries from R7:R22 that contain mixed details but cost still listed in AC7:AC22. I do not want to add columns and subdivide AC7:AC61 further by using the above as it would mean 20 more columns for the sake of 28 entries

I thought if I prefix the relevant entries in R7:R22 with “Office” I could use =(LEFT($R$7:$R$22,6)="Office" as a substitute for =AB$47

Below is what I entered as array but it is not working
= IF(SUMPRODUCT(--($R$7:$R$22=(LEFT($R$7:$R$22,6)="Office")))>=ROWS(S$48:S48),INDEX($AC$7:$AC$22,SMALL(IF($R$7:$R$22=( LEFT($R$7:$R$22,6)="Office"),ROW($R$7:$R$22)-ROW($R$7)+1),ROWS(S$48:S48))),"")

Any advice would be most welcomed

julhs

Hello juhls, try this version in AB43 confirmed with CTRL+SHIFT+ENTER and copied down

=IF(COUNTIF($R$7:$R$22,AB$42&"*")>=ROWS(AB$43:AB43), INDEX($AC$7:$AC$22,SMALL(IF(LEFT($R$7:$R$22,LEN(AB$42))=AB$42,ROW($R$7:$R$22)-ROW($R$7)+1),ROWS(AB$43:AB43))),"")

That will pick up all column AC values where the corresponding entry in column R begins with the text in AB42