Forum Discussion
Excel IF Statement
=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.
=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.
- 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
=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.
- Everything worked well. More complicated formula than I expected! Thank you.
