Difficult Lookup

[Kenshin]

Hi there, desperate need help from expert again, please take a look at my file, regards


Kenshin

 

[vletm]

Kenshin
If something starts with 'd'-word then
could it do other way?

 

[coolkiran]

@Kenshin

try Sumifs...

 

[Kenshin]

Thanks for the reply but i do need the formula and i don't want any changes on the table

 

[rahulshewale1]

Hi @Kenshin ,

you will get desired output using power query and pivot table .


See if it is ok ?

if you have Excel 2010 or excel 2013 then you have to download power query tool from Offical Microsoft Site.

if you have excel 2016 or Office 365 then it is in built in .


Regard
Rahul shewale

 

[vletm]

Kenshin
Thanks for the reply but i do need the formula and i don't want any changes on the table
Do that mean, Your table would be always same?

 

[Peter Bartholomew]

Like the others, in reality, I would adjust the input data format. However, to accept the challenge, here are a couple variants on a solution. Each one is given by a single array formula and shares the same set-up costs.

= IFERROR( INDEX( area.sales, MATCH( item, area.commodity, 0 ) ), 0 )
or
= SUMIFS( area.sales, area.commodity, item )

In either case the set-up defines the ranges within the original data table associated with each of the areas
= INDEX(Sales, start) : INDEX(Sales, end)
= INDEX(Commodity, start) : INDEX(Commodity, end)
where both 'start' and 'end' are indices returned by MATCH
= IFERROR( MATCH( area.previous, Areas ), 0 ) + 1
= MATCH( area.current, Areas ) - 1

 

[bosco_yip]

Or....................

In "formula result" B30, array formula copied across and down :

{=IFERROR(VLOOKUP(B$29,IF(IFERROR(LOOKUP(ROW($4:$17),ROW($4:$17)/($A$4:$A$17<>""),$A$4:$A$17),"Area 0")="Area "&RIGHT($A30)-1,$B$4:$C$17),2,0),0)}

p.s. array formula to be confirmed by enter with SHIFT+CTRL+ENTER

Regards
Bosco

 

[Kenshin]

Thank you all for the reply, thanks Bosco, your formula always amazed me, thanks again