Need help writing excel formula please

MrBK

New Member
Joined
Nov 25, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hello, I'm stuck writing a excel formula and need assistance and hopeful someone can help please!

I'm trying to write a formula that I can drag down column "P", that will analyze that row from (B:O) for the cell that has the first "1" in it, from left to right order, and return the header number listed in that column that's under the date in row 2. Thanks for your assistance!

Example 1: Row 3 has the first "1" in column "C" from (B3:O3), so it would return the header "13" in cell C2.
Example 2: Row 4 has the first "1" in column "B" from (B4:O4), so it would return the header "14" in cell B2.
Example 3: Row 5 has the first "1" in column "K" from (B5:O5), so it would return the header "5" in cell K2.
Example 4: Row 9 does not have a "1" in it from (B9:O9), so it would return a "0".
 

Attachments

  • Excel Help Needed.jpg
    Excel Help Needed.jpg
    65.1 KB · Views: 2

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,502
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

See if this helps

20 11 25.xlsm
BCDEFGHIJKLMNOP
1
21413121110987654321
3111113
411114
515
60
71113
First 1
Cell Formulas
RangeFormula
P3:P7P3=INDEX(FILTER(B$2:O$2,B3:O3=1,0),1)
 
Solution

MrBK

New Member
Joined
Nov 25, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Thanks for the assistance Peter!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,502
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the follow-up. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,961
Messages
5,575,252
Members
412,651
Latest member
caitlincole
Top