If two cells match, return value from third- one of the "matching" cells is vertically merged

Question

I have a contact list from hell that needs cleaning up. The basic problem is this: the company name is merged vertically and there multiple rows of information for each company (with blank cells everywhere).

Screenshot of Excel Issue

I have created a second sheet and was successful in using a formula I found on stack overflow for getting the data from the 2nd column (where the data was on the top of 3 rows):

=INDEX('Sheet1'!D:D,MATCH(A4,'Sheet1'!A:A,0))

However, the same formula doesn't work when the needed data is on the second row.

I have thought about trying to unmerge column A, then duplicating the data from the merged cell to each unmerged cell. But I'm afraid that having 3 matching cells will return blanks with the formula above.

*I am not a programmer in the least, but I've found stack overflow very helpful for working with Excel. Thanks for your patience with me and I very much appreciate any help you could give.

Answer 1

A single sample is not really enough to explain the issue, but the following might help, where the single example is highlighted:

ColumnA is after unmerging. The formula in D1 (copied across to E1 and D1:E1 then copied down to suit) is:

=TRIM(IF($A1<>"",B1&" "&B2&" "&B3,""))