Transpose a range based on a conditionial column

[Lolo]

Hello,

I have a problem, and don't find the right formulas to tacke it
Hope you could help me.

I have a range which describe a correspondance of city, by company.
For exemple the city called Saint-Estève (real name) is known in the referential of the company C1 as 'saint esteve', and for company C2 'SAINT ESTEV' (it is just an example)
So this is a basic city correspondance range.

I would like to transpose this input range, formatted this way (I don't have the choice) with for each real city, the correspondance by company (so 1 column by company), like shown in the attached file.

I know how to do this with VBA, but I'm quite sure it is possble with just formula. But it is beyond my knowledge.

Thank you for your help

 

[Hui]

L4: =IFERROR(INDEX(t8data[City],MATCH($K4&L$3,t8data[RealCity]&t8data[Company],0)),"") Ctrl+Shift+Enter
Copy across and down

Refer attached file:

You may want to read:
http://chandoo.org/wp/2010/11/02/multi-condition-lookup/

 

[Somendra Misra]

Hi,

Just another non -array formula approach. But the only problem is with my formula and most probably with @Hui approach is, both the formulas is with cities with Blank values, and if you have multiple cities with Blanks than the formula needs to be changed.

Regards,

 

[Lolo]

Hui, Somendra, Thank you, for your quick answer and the link. I will have a look.

In the same time I thought of a solution with a helper column based on the idea of the concatenation like the solution of Hui.
But no need to have array formula. See attached file.
And to answer to the problem raised by Somendra about blank values, the column 'Empty' enables to exclude lines without match. Theses empty values which can be managed separately.

@Somendra : I don't really understand your formula
Could you explain a little bit more please ?