CODEWITHSUNDEEP
excelexcel-formula
Chipmunk_da
Chipmunk_da

Reputation: 507

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

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

Upvotes: 0

Views: 984

Answers (1)

Jos Woolley
Jos Woolley

Reputation: 9062

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.

Upvotes: 2

Related Questions