Index & Match formula for a number in a string

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Re: Index & Match formula for a number in a string


    Try:


    =IF(E117="","",IFERROR(IF(RIGHT(E117)="*","*","")&INDEX($Z$14:$Z$25,MATCH(MID(SUBSTITUTE($E117,"*",""),FIND("v",SUBSTITUTE(E117,"*",""))+1,2)+0,$Y$14:$Y$25,0))&IF(LEFT(E117)="*","*",""),INDEX($Z$14:$Z$25,MATCH(LEFT(" "&SUBSTITUTE(E117,"*","")&" ",FIND("v"," "&SUBSTITUTE(E117,"*","")&" ")-1)+0,$Y$14:$Y$25,0))))

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Index & Match formula for a number in a string


    Excellent works a treat. I wish there was a star or something to mark my grateful thanks.

    Re: Index & Match formula for a number in a string


    Quote

    I wish there was a star or something to mark my grateful thanks.


    Now worries. Saying "Thanks" is thanks enough :)

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Index & Match formula for a number in a string


    Heavens I have just spotted a small problem with the asterisk in that the formulas are great but when you put in the “*” it goes against both teams rather than one..


    What I would like is that if I put in “1v2 the left hand name would appear as “CAN (asterisk 1st, name 2nd) but the right hand name would have no “*” and if I put in 1v2* then the right hand name would appear say as SCO* but the first name would have no asterisk against it in other words name 1st then followed by the asterisk.


    Oh I am sorry to bother you again, can this be done?

    Re: Index & Match formula for a number in a string


    I am not sure I understand anymore :question:


    Can you post several possible combinations and what you would expect from each?


    Maybe post a sample workbook so your lookup values are in place, etc....

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Index & Match formula for a number in a string


    Ok, now that I understand better what you are doing... you need 2 separate formulas.... one for left of the v, and one for the right side.


    So, in D134:


    =IF(E113="","",IFERROR(IF(LEFT(E113)="*","*","")&INDEX($Z$14:$Z$25,MATCH(LEFT(SUBSTITUTE(E113,"*",""),FIND("v",SUBSTITUTE(E113,"*",""))-1)+0,$Y$14:$Y$25,0)),""))


    and in F134


    =IF(E113="","",IFERROR(INDEX($Z$14:$Z$25,MATCH(MID(SUBSTITUTE($E113,"*",""),FIND("v",SUBSTITUTE(E113,"*",""))+1,2)+0,$Y$14:$Y$25,0))&IF(RIGHT(E113)="*","*",""),""))


    I also checked for 11 and 12 entries and it works....

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Index & Match formula for a number in a string


    That's it, sorry I didn't upload in the first place. Now where's the star button! Many regards

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login