Lookup

[swabeeh]

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

Thank you

[#2]

@swabeeh 

( I dont want to use Pivot table)

Why not?

[#3 · Accepted solution]

@swabeeh 

See the attached demo workbook.

[#4]

@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 

[#5]

Using @HansVogelaar 's workbook

[#6]

Thank you @HansVogelaar

[#7]

Thank you very much sir, Very helpul