Excel IF Statement

[BobWhiteJax]

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 in column D IF they have a certain prefix in column B, for example 39.

The statement needs to handle a range. I tried =IF(B7=39-000:39-999,D7,0) hoping that all values in Column D would be selected if Column B value was in this range. If not in the range, assign

Column A                                        Column B           Column D

Cost Code Name                            Cost Code          Total Hours

10" STORM	39-110	14h 46m
12" DRAIN BASIN	39-312	112h 29m
12" Reuse	43-212	5h 15m
12" Stab. Asphalt Subgrade	31-100	314h 43m
12" STORM	39-112	958h 22m
12" Water	43-112	390h 23m
15" HDPE	39-115	540h 19m
15" RCP Storm	39-915	70h 05m
18" DRAIN BASIN 7'	39-618	16h 42m
18" ENDWALL  STRUCT (0-4	39-450	98h 31m
18" MES	39-318	29h 25m
18" Precast MES	39-418	6h 16m
18" RCP Storm	39-918	15h 52m
18" STORM	39-118	1076h 15m
2" Gate Valve Water	43-402	4h 59m
2" HDPE POLY - IRRIGATION	43-102	14h 37m
2" Reuse Water	43-202	64h 46m
24" HDPE	39-124	1140h 50m
24" MES	39-324	210h 38m
24" RCP Storm	39-924	235h 04m
3" BFP & Hot Box	43-404	8h 19m

[#2]

@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.

[#3]

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

[#4 · Accepted solution]

@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.

[#5]

Everything worked well. More complicated formula than I expected! Thank you.