If cell contains value then 'Column header'

Question

Looking for help

 A       B       C       D       E        F
1               Food    Petrol   Rent   Wages
2       **Food** 12
3       **Petrol**        35
4       **Rent**                  120
5       **Wages**                       300

I have a formula that copies values into cells C2:F5 -onwards but I'm having difficulty coming up with a formula that copies the column header "Food/Petrol/Rent" into the B column if there is a value in the appropriate cell in the column

So if C2 has a value then B2=C1 If F5 has a value then B5= "Wages"

currently I have 38 Columns and nearly 1000 Rows, it could get complicated to do it all manualy

Thanks in advance

Answer 1

There are a number of ways to do this. You describe having 38 columns, so some form of lookup is the simplest approach. You can do this with INDEX and MATCH:

The formula in B2:

=INDEX($C$1:$F$1,MATCH(0,ISBLANK(C2:F2),0))

Notice the braces around the formula in the image. This is an array formula, so you need to enter it using Ctrl + Shift + Enter instead of just Enter. And of course, you need to adjust the ranges to match your data.

How it works:

MATCH looks for the first nonblank entry, in which case ISBLANK will return False, which OpenOffice displays as 0 (True would display as 1). MATCH returns the column position within the range where the match was found. The 0 parameter at the end looks for an exact match.

INDEX returns the value from the first row in the corresponding column position found by MATCH.

You can copy and paste the formula down column B once it's entered.

UPDATE:

You've indicated in comments that your actual spreadsheet doesn't have empty cells where there are no numbers. Rather, they contain formulas that display a blank cell. In this case, ISBLANK won't recognize it as blank. We can handle that by just testing for a number value and looking for the first True condition. So the B2 formula would be:

=INDEX($C$1:$F$1,MATCH(1,ISNUMBER(C2:F2),0))

Again, this is an array formula, so you need to enter it using Ctrl + Shift + Enter instead of just Enter. I just verified that this formula works in this example when the "empty" cells aren't actually blank.