Minif Function with Partial match in a range "How to include Left function in Min if"

[kuldeepjainesl]

Hello,

I know the Min if can be achieved by something like

{=MIN(IF($A$2:$A$20=$B$2,$D$2:$D$20))}

Now i am stuck in the if condition where i only need to match first 7 Characters of the range to specified value. I tried

{=MIN(IF(LEFT(Severity_level,7)="*"&'Variable Used'!B8&"*",Date_Severity_Assign))}

but i know this is wrong.

How to include Left function in Min if

 

[kuldeepjainesl]

Sample File if this help

 

[NARAYANK991]

Hi Kuldeep ,

See the file now.

Narayan

 

[kuldeepjainesl]

Great...it working but i am lost in the way you achieved it. May you please help me digest it.

Thanks Narayan

 

[NARAYANK991]

Hi Kuldeep ,

There is nothing much to it ; I have used a lengthy formula only to cater to the two different situations ; I could have used a shorter version , and it would have worked equally well , but I thought you could see the two different situations taken care of separately.

1. Where you enter a complete string which has an exact match ; in this case , the operative portion of the formula would be :

MIN(IF($A$2:$A$20=$C2,$B$2:$B$20))

If there is an exact match , in column A , for what ever data string you have entered in column C , then the result will be a date value ; since any date value is greater than 0 , the first IF test will be satisfied , and the earliest date will be returned.

Suppose there is no exact match for the data string that has been entered , the first IF test will return 0 , and then the left-most 'x' characters of the entered data string is used to check for a partial match , and the corresponding date is returned through the following portion :

MIN(IF(LEFT($A$2:$A$20,LEN($C2))=$C2,$B$2:$B$20))

Instead of using 7 as a static value , I have put in LEN($C2) , so that if you enter a 5 character string , the left-most 5 characters of the data in column A will be used for matching purposes.

Narayan

 

[kuldeepjainesl]

Humm...i was being puzzled by first condition where i was thinking that this condition is not going to be true then way you have included. Now understood. You start thinking from where we stop and that's make you "NARAYAN"

• Narayana, an Indian name, an important Sanskrit name for Vishnu "of Excel"

 

[Somendra Misra]

Hi Kuldeep,

Just another array formula approach, try below array formula with Ctrl+Shift+Enter.

=MIN(IF((SEARCH(C2,$A$2:$A$20)=1)*($B$2:$B$20<>""),$B$2:$B$20))

Regards,

 

[kuldeepjainesl]

Wow...That also Worked...Somendra,

This prove there are many way of achieving the same objective and a lot to learn and understand with you guys. Thanks a lot.