asked on

# Excel combination cell help. Loop?

Parent Child Example.xlsxHi all,

Trying to create a combined cell that looks at column A and B and combines these values separated by an underscore. The problem is column A has a sort of header to the section them empty until next section (see attached). Is there a way to say if the column C = If A is not blank, A&"_"&B, if A is blank go up one row until a not null field is found for A. I know there is a more efficient and elegant way of framing that as well. Thank you!

Based on the sample data, you could do something simple like:

=IF(B2="","",IF(AND(A2="",A1<>""),A1&"_"&B2,LEFT(C1,FIND("_",C1))&B2))

If your workbook isn't working as desired, please post it (or something that reproduces the problem) so we can fix it.

Please make sure that your Excel is in Automatic calculation mode, as copying down a formula in Manual calculation mode won't make it update. You change this setting in the File...Options...Formulas ribbon item.

Parent-Child-Example.xlsx

Just playing with different parent names and it looks like it doesn't matter on sort order for any of the above suggestions. I knew Rory's suggestion would not have any sort order dependency as it just looks at the row above if A is blank whereas thought that my and Brad's suggestions would have. By the way, Brad's LOOKUP is doing the same as I described with my INDEX/MATCH combination but with one function. I am in the habit of using INDEX/MATCH combination rather than lookup as the combination is more versatile and less volatile but neither of those matter where lookup and return value are in the same column.

```
=IF(B2="","",INDEX(A$1:A2,MATCH("zzzzzzz",A$1:A$2))&"_"&B2) wrong
=IF(B2="","",INDEX(A$1:A2,MATCH("zzzzzzz",A$1:A2))&"_"&B2) correct
=IF(B2="","",LOOKUP("zzzzz",A$1:A$2)&"_"&B2) wrong
=IF(B2="","",LOOKUP("zzzzz",A$1:A2)&"_"&B2) correct
```

Open in new window