Column to multi-row combination using IF Excel Function


Welcome to Excel Jet Consult blog post. In this, we learn how to flatten our data from column to multiple rows combination using IF Excel Function.


Do watch the tutorial video below

Sample Data

In the caption below, we have list of Countries, States/Provinces and Population

The goal of this tutorial is to write an IF formula that will, in cell D2, return the State/Province and Population in cells B2 and C2 respectively. In cell D3, we expect to see the result from D2 and concatenation of the values from cells B3 and C3 respectively; and so on.


To achieve the above explanation, execute the formula in cell D2

  • =IF(A2=A1,D1&”|| “&B2&” “&TEXT(C2,”#,##0″),B2&” “&TEXT(C2,”#,##0”))
  • CTRL + Enter

In the caption below, we can see that we achieved what we set out to do.

See you in the next video


Posted in Blog.

Leave a Reply

Your email address will not be published. Required fields are marked *