Excel - How can I find the last column with a value and pull the corresponding column header?

0
votes

I have an Excel table with a column containing distinct IDs and then some boolean columns with 1s and 0s. For each row (ID), I'm looking for a formula to find the last boolean column with a value of 1 and then pull the corresponding column header. I've added a screenshot of the input (blue columns) and the desired output (yellow column). I think I can use the offset formula for pulling the column headers but can't figure out how to determine the last boolean column with a 1.

Thanks

enter image description here

1
excelexcel-formula
Use LOOKUP() or XLOOKUP() depending on your version of Excel. What have you tried? – JvdV
I tried xlookup as =XLOOKUP(1, <cell range of 1s and 0s to search>, <cell range of column headers>) but this always gives me Day 1 for every row – Chipmunk_da

1 Answers

2
votes

Assuming that table has its top-left cell in A1, In I2:

=XLOOKUP(1,B2:H2,B$1:H$1,,,-1)

Or, non-365:

=LOOKUP(1,0/B2:H2,B$1:H$1)

and copy down.