+ Reply to Thread
Results 1 to 20 of 20

Multiple IF functions combined with MID

  1. #1
    Registered User
    Join Date
    07-06-2016
    Location
    Gr
    MS-Off Ver
    2007 - 2010
    Posts
    11

    Multiple IF functions combined with MID

    Hello everyone and i hope i you can help me with my problem.
    I'll try to keep it simple. So let's do this!

    I got 2 columns lets call them A and B.

    A
    is almost always an 8digit number maybe less but never more like 12345678
    or
    is with a letter like 123w4567

    B

    is always checking the 3rd and 4th digit of A column and shows a sentence for example

    A column number is 12305544 so the 3rd and 4th is 30 so in column B i type CR15

    i want that to be automatic. But now comes the real problem i got around 50 codes like 30=CR15, 90=CR20 and goes on.

    What can i do???

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Multiple IF functions combined with MID

    You can set up a table showing your codes in one column and their meanings (sentences) in the next column - suppose this is in columns X and Y.

    Then your formula in B2 can be:

    =IFERROR(VLOOKUP(MID(A2,3,2),$X:$Y,2,0),"")

    Then copy this down.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    07-06-2016
    Location
    Gr
    MS-Off Ver
    2007 - 2010
    Posts
    11

    Re: Multiple IF functions combined with MID

    You mean like this X=1230567 Y=cr15 and a 3rd column has your function?

    And im incapable of combining more than 1 Function
    I always get an error and since im a regular excel user i would like more detail so i can understand why i use something and how it works.
    I know i ask a lot but its all for a greater purpose.
    Thank you for your quick response i apreciate it. I was thinking something like =IF((MID(A2,3,2)=30;cr20;"(MID(A2,3,2)=15")
    and combine it with multiple if and then but i dont know if it is possible.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,003

    Re: Multiple IF functions combined with MID

    Using Pete's example:

    X2=30, Y2=CR15

    X3=40, Y3=????

    etc

    =IFERROR(VLOOKUP(MID(A2,3,2),$X:$Y,2,0),"")

    So you "lookup" 30 (MID(A2,3,2), find the match in column X and return the corresponding value from Y

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Multiple IF functions combined with MID

    You may have to convert MID(...) to a number to match the lookup table value.

    =IFERROR(VLOOKUP(--MID(A2,3,2),$X:$Y,2,0),"")
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Registered User
    Join Date
    07-06-2016
    Location
    Gr
    MS-Off Ver
    2007 - 2010
    Posts
    11

    Re: Multiple IF functions combined with MID

    So i make 2 columns with X being the numbers and Y be the codes. Then i use =IFERROR(VLOOKUP(MID(A2,3,2),$X:$Y,2,0),"") at B column to check if mid(A2,3,2) is in X column if it is it shows the Y result in the same line?

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Multiple IF functions combined with MID

    I thought my post was quite clear - you put the codes and their meanings in columns X and Y (for example):

    30 ..... CR15
    90 ..... CR20

    and so on, and you put the formula in cell B2 (with your 8-digit numbers in column A, starting with A2), and copy the formula down column B as far as you need to.

    Hope this helps.

    Pete

  8. #8
    Registered User
    Join Date
    07-06-2016
    Location
    Gr
    MS-Off Ver
    2007 - 2010
    Posts
    11

    Re: Multiple IF functions combined with MID

    I did it and it just show me the =IFERROR(VLOOKUP(MID(A2,3,2),$X:$Y,2,0),"") in the cell.
    I think is missing some " am i right?

  9. #9
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Multiple IF functions combined with MID

    That usually means the cell is formatted as text.

    Change the cell format to General then re-enter the formula.

  10. #10
    Forum Contributor
    Join Date
    05-06-2016
    Location
    Cluj, Romania
    MS-Off Ver
    2007-2010
    Posts
    220

    Re: Multiple IF functions combined with MID

    Hi,
    check your regional settings and if necessary change "," with ";"
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    07-06-2016
    Location
    Gr
    MS-Off Ver
    2007 - 2010
    Posts
    11

    Re: Multiple IF functions combined with MID

    Yup regional settings was the issue for error fuction problem.
    Now the fuction works but i get blank as results :/

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Multiple IF functions combined with MID

    Try this...

    =IFERROR(VLOOKUP(--MID(A2;3;2);$X:$Y;2;0),"")

  13. #13
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Multiple IF functions combined with MID

    Make the change suggested by Tony in Post #5, i.e. put double-minus in front of the MID function to convert the text string that it returns to a number:

    =IFERROR(VLOOKUP(--MID(A2;3;2);$X:$Y;2;0);"")

    Hope this helps.

    Pete

  14. #14
    Registered User
    Join Date
    07-06-2016
    Location
    Gr
    MS-Off Ver
    2007 - 2010
    Posts
    11

    Re: Multiple IF functions combined with MID

    Guys thank you for the help i managed to make it work in a way =IFERROR(VLOOKUP(--MID(A2;3;2);$X:$Y;2;0);"") was the correct Fuction but it does show me the same results in some cells even if the number is different. I'll simply do it manually. Sorry for being a pain in the @#$ and dont try it more.
    Thanks again

  15. #15
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Multiple IF functions combined with MID

    Can you post a SMALL sample file that demonstrates that?

  16. #16
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Multiple IF functions combined with MID

    Check your Calculation Options under the Formulas tab - do you have it set to Manual? If so, change it to Automatic, then save the file.

    Hope this helps.

    Pete

  17. #17
    Registered User
    Join Date
    07-06-2016
    Location
    Gr
    MS-Off Ver
    2007 - 2010
    Posts
    11

    Re: Multiple IF functions combined with MID

    http://www.excelforum.com/attachment...1&d=1467964163

    For an unknown reason i couldnt find it at the attachment files so for the 1 - 2 that want to try hard :D
    The G column is the one with the fuction that doesnt work properly.

    Thanks again and im sorry for the late response
    Attached Files Attached Files

  18. #18
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,003

    Re: Multiple IF functions combined with MID

    Perhaps

    =IFERROR(VLOOKUP(--MID(F1,3,2),$X:$Y,2,0),"")

  19. #19
    Registered User
    Join Date
    07-06-2016
    Location
    Gr
    MS-Off Ver
    2007 - 2010
    Posts
    11

    Re: Multiple IF functions combined with MID

    Yeah i forgot to fix that because i created a new one for testing. Seems to be working so case closed.

    You are the best guys!

  20. #20
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Multiple IF functions combined with MID

    You're welcome. We appreciate the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Multiple IF statements, combined with Index, Small and Row functions
    By wallyjuk in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-18-2016, 10:02 AM
  2. Combined IF and LOOKUP functions - Help please!
    By DaithiMacGiolla in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 06-13-2014, 11:20 AM
  3. Concatenate, If , And Functions combined
    By medlock_1989 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 06-30-2013, 06:46 PM
  4. Can sumif and Max functions combined into one?
    By ReconMan in forum Excel General
    Replies: 4
    Last Post: 09-20-2011, 11:29 AM
  5. Can the IF and VLOOKUP functions be combined?
    By WG1 in forum Excel General
    Replies: 3
    Last Post: 08-29-2009, 06:58 AM
  6. Combined Functions
    By fncuis in forum Excel General
    Replies: 4
    Last Post: 08-22-2005, 07:16 PM
  7. [SOLVED] combined two countif functions
    By Geoff in forum Excel General
    Replies: 2
    Last Post: 08-11-2005, 07:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1