VLOOKUPS and Line Breaks

Have you ever done a VLOOKUP only to find that the words run into each other, but when you look at the source, the words are beautifully spaced? You end up with something like: "Priced by volume:$100 buy in volume of 200$150 buy in volume of 300$200 buy in volume of 500"? If you look at the source origin, you will find that line breaks were made within the cell to make the layout fine for the row height and wrap text combination. But it's not so good for when you are trying to pull in the results via VLOOKUPS.

Again, here's a mockup of something I'm trying to create as an example:

Yes, I changed some formulas and formats and added some new stuff - this is a work in progress.

The part I want to focus on today is the "Upcoming Changes" column in the "Minimum Wage" tab. Looking at that column, the information looks beautifully laid out, with proper line arrangement in the cell. But when you do a simple VLOOKUP, it gets all bunched up in one line and does not look good for presentation. The reason for the beautiful layout in the source document is because of the line breaks made within the cell (done by keystroke ALT-Enter).

So how do I get around this?

Basically with:

=IFERROR(SUBSTITUTE(INDEX('Minimum Wage'!$F$1:$I$150,MATCH($E35,'Minimum Wage'!$G$1:$G$150,0),MATCH(L$30,'Minimum Wage'!$F$1:$I$1,0)),"$",CHAR(13)&"$"),"")

(This formula is found in the minimum wage section, column L.)

Let's break this down.

INDEX('Minimum Wage'!$F$1:$I$150,MATCH($E35,'Minimum Wage'!$G$1:$G$150,0),MATCH(L$30,'Minimum Wage'!$F$1:$I$1,0))   This INDEX formula looks within the range F1:I150 in "Minimum Wage" tab, looking for the intersection of "Key municipalities" and "Upcoming Changes". The two MATCHes find the municipality (the first MATCH) and the "Upcoming Changes".

Now we wrap whatever "Upcoming Changes" INDEX finds for us with the SUBSTITUTE function. The SUBSTITUTE function will substitute all $ with CHAR(13)&$. That CHAR(13) is the line break. You might have to experiment between CHAR(10) and CHAR(13) but I'm finding CHAR(13) to work best for me. So every time the function finds a $ sign, the function will add a line break at the $ sign.

And it works like a dream.