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.

     

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Jun 06, 2023

BobWhiteJax 

=IFERROR(INDEX($E$7:$E$165,SMALL(IF(NUMBERVALUE(LEFT($B$7:$B$178,2))=$G$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 search criteria 39 is entered in cell G5 and can be changed dynamically. I've entered the Total in column E in order to avoid problems with the merged columns C and 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