Using IF with MID

[i_m_rock]

I am a new member and new to excel. I needed to find out how to use IF and MID functions together. =IF MID(B2,4,1) =5 Then return value 5, if it =1 then return value "1.75" etc. Is this possible? Also i have 5 other fields that will update based on the returned value. Will that cause errors? Thanks in advance!

 

[Joe4]

Welcome to the Board!

You can imbed up to 7 levels in an IF statement. Your IF would look something like:

=IF(MID(B2,4,1)=5,5,IF(MID(B2,4,1)=1,1.75,...))

If you have more than that, take a look at using something like VLOOKUP (Excel's built-in help on functions has details and example of that).

I have also seen some people using some other clever methods using arrays, though that quite isn't my specialty!

 

[Jonmo1]

Welcome to the board.

Sure it's possible, but judging by your description, I wouldn't use IF..
It looks like you have more than 2 options for value to return based on what the 4th character is. I would use a lookup.

=LOOKUP(MID(B2,4,1)+0,{1,5},{1.75,5})

It takes the 4th character of B2 (the +0 makes it a number isntead of a text string).
Finds that number in the first array {1,5}
Returns the corresponding value from the 2nd array {1.75,5}

Make sure the 1st array is listed in ASCENDING order, smallest to largest from left to right.

Hope that helps..

 

[Joe4]

There it is. That was the method I was thinking about. Thanks Jon.

 

[i_m_rock]

That woked perfect! Thanks for the quick reply from you both!

 

[Jonmo1]

Also, the lookup may need some tweeking / error handling.

As the formula is, it will error if the 4th character is NOT a number, OR if there IS no 4th character, like the string is only 2 or 3 characters long..

Also, if say the 4th character is a 3 (which is NOT in the lookup array), it will return the closest match (largest # that is less than or equal to the lookup). So in this case it will return as if the 4th character was a 1.


all this can be adjusted for if you need it. Probably more like an Index/Match type of formula

 

[i_m_rock]

Is there a way to have the value return " " empty if the first field is empty. It shows #value now if the field is empty. Not a big deal, just trying to clean it up. Thanks again.

 

[Jonmo1]

Sure, but it might be better to test if the Length of B2 is less than 4 (because that will cause error as well).

=IF(LEN(B2)<4,"",LOOKUP(MID(B2,4,1)+0,{1,5},{1.75,5}))

 

[i_m_rock]

Also, the lookup may need some tweeking / error handling.

As the formula is, it will error if the 4th character is NOT a number, OR if there IS no 4th character, like the string is only 2 or 3 characters long..

Also, if say the 4th character is a 3 (which is NOT in the lookup array), it will return the closest match (largest # that is less than or equal to the lookup). So in this case it will return as if the 4th character was a 1.


all this can be adjusted for if you need it. Probably more like an Index/Match type of formula

I was hoping to avoid this by formating the cell to limit it to the number of characters that I need (00-0000)

 

[i_m_rock]

That got it. Thanks again, you guy's are FAST!