SOLVED

Lookup

Copper Contributor

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 with column headings “Product” & name of “city”( city 1, city2, city3)

So the product column in the table will show all the “product” name and under respective “city” column the “qty” also

 

simply i want vertical city data lookup in to horizontal data

swabeeh_0-1711959916233.png

 

Thank you

6 Replies

@swabeeh 

 

( I dont want to use Pivot table)

Why not?

best response confirmed by swabeeh (Copper Contributor)
Solution

@swabeeh 

See the attached demo workbook.

@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:

 

Using @Hans Vogelaar 's workbook

Thank you @Hans Vogelaar
Thank you very much sir, Very helpul
1 best response

Accepted Solutions
best response confirmed by swabeeh (Copper Contributor)
Solution

@swabeeh 

See the attached demo workbook.

View solution in original post