Get the lowest range of columns

[gigi79]

Hello All,

I have three columns (D, E, and F) and any of those columns could have a value of "0-30", "31-60", "60-90", "91-180", "181 - 365" and "365"+.

I have a fourth column in which I need the lowest for those columns,

so of column D has 31-60, column E has 365+ and column F has 0-30, I need the fourth column (column B) to show 0-30.

I tried the Min, Small, and a few other formula mixes included nested "if" statement but I'm getting nothing but errors.
Any help would be appreciated.

 

[ExcelToDAX]

Assuming you have Office 365 functions, how about

=LET(rng,D1:F1,secondPart,SUBSTITUTE(INDEX(SORT(VALUE(TEXTAFTER(SUBSTITUTE(rng,"+","-366"),"-"))),1,1),"366","365+"),IF(secondPart="365+","365+",CONCAT(INDEX(SORT(VALUE(SUBSTITUTE(TEXTSPLIT(rng,"-"),"+",""))),1,1),"-",secondPart)))

 

[gigi79]

=LET(rng,D1:F1,secondPart,SUBSTITUTE(INDEX(SORT(VALUE(TEXTAFTER(SUBSTITUTE(rng,"+","-366"),"-"))),1,1),"366","365+"),IF(secondPart="365+","365+",CONCAT(INDEX(SORT(VALUE(SUBSTITUTE(TEXTSPLIT(rng,"-"),"+",""))),1,1),"-",secondPart)))

this isn't working properly. Can you help me understand what it's doing? (Red is the formula result it should be the ones circled in blue).

I think some of the issue is that the columns can be either "181-365" or "365+" there is no 366 (there should be, but the date ranges overlap - not my doing or comtrol)

 

[dreid1011]

Perhaps this:

Book1 11-27-2023.xlsx
	D	E	F	G	H
1
2	91-180	31-60	60-90		31-60
3	31-60	365+	0-30		0-30
4	365+	365+	365+		365+
5	181-365	181-365	365+		181-365
6	91-180	181-365	365+		91-180
Sheet3

Cell Formulas
Range	Formula
H2:H6	H2	=LOOKUP(MIN(IF(IFERROR(SEARCH("+",$D2:$F2),0),365,LEFT($D2:$F2,FIND("-",$D2:$F2)-1)*1)),{0,31,60,91,181,365},{"0-30","31-60","60-90","91-180","181-365","365+"})

 

[gigi79]

Perhaps this:

Book1 11-27-2023.xlsx
	D	E	F	G	H
1
2	91-180	31-60	60-90		31-60
3	31-60	365+	0-30		0-30
4	365+	365+	365+		365+
5	181-365	181-365	365+		181-365
6	91-180	181-365	365+		91-180
Sheet3

Cell Formulas
Range	Formula
H2:H6	H2	=LOOKUP(MIN(IF(IFERROR(SEARCH("+",$D2:$F2),0),365,LEFT($D2:$F2,FIND("-",$D2:$F2)-1)*1)),{0,31,60,91,181,365},{"0-30","31-60","60-90","91-180","181-365","365+"})

this works! Thank you!

 

[dreid1011]

You're welcome, and happy to help.

 

[gigi79]

Perhaps this:

Book1 11-27-2023.xlsx
	D	E	F	G	H
1
2	91-180	31-60	60-90		31-60
3	31-60	365+	0-30		0-30
4	365+	365+	365+		365+
5	181-365	181-365	365+		181-365
6	91-180	181-365	365+		91-180
Sheet3

Cell Formulas
Range	Formula
H2:H6	H2	=LOOKUP(MIN(IF(IFERROR(SEARCH("+",$D2:$F2),0),365,LEFT($D2:$F2,FIND("-",$D2:$F2)-1)*1)),{0,31,60,91,181,365},{"0-30","31-60","60-90","91-180","181-365","365+"})

I have a follow up question...one of the fields contains an error (it is a vlookup) which means the result formula is an error, is there a way to tweak that to ignore any error column and choose the value from the others?

(Result formula is in the red circle) - column D should be ignored since it is an error

 

[dreid1011]

Yeah, wrap the whole formula in IFERROR(), like this perhaps:

=IFERROR(LOOKUP(MIN(IF(IFERROR(SEARCH("+",$D2:$F2),0),365,LEFT($D2:$F2,FIND("-",$D2:$F2)-1)*1)),{0,31,60,91,181,365},{"0-30","31-60","60-90","91-180","181-365","365+"}),"")