CODEWITHSUNDEEP
excel
Molly Engstrom
Molly Engstrom

Reputation: 11

return the nearest number value above current cell within a list in Excel 2010

This is a little difficult to explain but I have a list of data, all in one column, that contains a department number and then below each department number is a long list of brands that correspond to that department number. So within this list there are multiple departments with brands listed underneath them.

My goal is to somehow return the value of the department next to each brand but because the department number is located above the list of brands and the data is changing each time I repull the report, the row numbers will not remain them same and there may be duplicate brand names, and I cannot figure out what formula to use because it is all contained in one column.

I need to come up with some type of formula that doesn't just search, but returns the value of the next number or value above the current cell and skips all of the text or blanks in between so it will grab the department number above it. Is there a way to do this or a better way to accomplish the goal of assigning each brand to the department above it while allowing for changing data each time I pull the report?

I tried replacing all of the text with 0's or blanks and then used the indirect function to say: =IF(BG24="",INDIRECT("BF" & ROW() - 1),""), but I assumed it would continue to loop and skip the zeroes or blanks until it found a number, but it did not, it just returned the zero value.

Upvotes: 0

Views: 2030

Answers (1)

Jerry
Jerry

Reputation: 71598

If I understand your problem correctly, the following formula should do it. Put it in cell B2. In cell A1, I'm assuming that there's the department number.

=IF(ISERROR(A1*1), IF(ISERROR(A2*1), B1, ""), A1)

This will work for a table like the following where no brands are numbers only:

1
Brand1
Brand2
2
Brand1
Brand2

To become:

1
Brand1   1
Brand2   1
2
Brand1   2
Brand2   2

Upvotes: 2

Related Questions