Forum Discussion

swabeeh's avatar
swabeeh
Copper Contributor
Apr 01, 2024

Lookup

I need to lookup excel ( I dont want to use Pivot table) I have date in one sheets with three columns “Product” , “qty sold”and “city” And in other sheet i want it in the way that, the table wit...
Excel
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Apr 01, 2024

swabeeh 

It depends upon the version of Excel that you use:

Dynamic array

= SUMIFS(qty, Product, UNIQUE(Product), country, TOROW(UNIQUE(country)))

LET/LAMBDA

= LET(
    productHdr, UNIQUE(Product),
    countryHdr, TOROW(UNIQUE(country)),
    SUMIFS(qty, Product, productHdr, country, countryHdr)
  )

GROUPBY/PIVOTBY

= PIVOTBY(Product, country, qty, SUM,,0,,0)

Other versions of Excel are available.

But I wouldn't recommend them :suprised:

 

Resources