Forum Discussion

BobWhiteJax's avatar
BobWhiteJax
Copper Contributor
Jun 06, 2023
Solved

Excel IF Statement

See table below. I have multiple codes that have a prefix of a two-digit number followed by a three-digit number. I am trying to complete and IF statement that will for example, take all the values i...
excel
  • OliverScheurich's avatar
    OliverScheurich
    Jun 06, 2023

    BobWhiteJax 

    =IFERROR(INDEX($D$7:$D$178,SMALL(IF(NUMBERVALUE(LEFT($B$7:$B$178,2))=F$5,ROW($B$7:$B$178)-6),ROW(A1))),"")

    You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. The formula is in cell F7 and filled across range F7:H47. The only change to the other formula is that this one refers dynamically to cells F5, G5 and H5 which hold the values 39, 41 and 43. I've removed the merged columns C and D in the attached file otherwise the formula doesn't work with reference to column D.

     

BobWhiteJax's avatar
BobWhiteJax
Copper Contributor
Jun 06, 2023
I probably did not explain it very well. On the spreadsheet I sent, I am looking to add three columns - say F, G, and H. I want all the 39 prefix hours (column D) to print in Column F, 41 prefix in Columnd D to come out on Column G and 43 prefix hours from Column D in Column H. The fact that each prefix has different three-digit numbers after the dash is what screwed me up. I could do it manually as it is only 173 rows of data but thought there could be an easier way. Appreciate your help and apologize for the confusion. Bob
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Jun 06, 2023

BobWhiteJax 

=IFERROR(INDEX($D$7:$D$178,SMALL(IF(NUMBERVALUE(LEFT($B$7:$B$178,2))=F$5,ROW($B$7:$B$178)-6),ROW(A1))),"")

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. The formula is in cell F7 and filled across range F7:H47. The only change to the other formula is that this one refers dynamically to cells F5, G5 and H5 which hold the values 39, 41 and 43. I've removed the merged columns C and D in the attached file otherwise the formula doesn't work with reference to column D.

 

  • BobWhiteJax's avatar
    BobWhiteJax
    Copper Contributor
    Jun 12, 2023
    Everything worked well. More complicated formula than I expected! Thank you.

Resources