Hello,

I have a spreadsheet (screenshot included) with three columns:

Column A:Number
Column B:Letter
Column C: Concatenate(Number,Letter)

Concatenate screenshot.png

In column C, I want to concatenate the Number and Letter columns by row. Let's use row 2 as an example. Obviously, to do a simple concatenation, you would just use the formula =concatenate(A2,B2). However, instead of using the value 'A2', I want this to be a lookup function where the lookup searches A2 for a value; if A2 has a value, then the first concatenated parameter will equal A2. If A2 has no value, then the parameter will search upwards in the A column for a value and will use that value for the concatenation.

What function can I use to look at a cell, use the value if there is one, and if there isn't, look upwards in the column until a value is found and use that one?