Index & Match formula for a number in a string

  • Can anyone help with an Index & Match formula that would allow me to find and match a specific number contained in a sequence which may contain any number between 1 and 12 or a letter.


    The cell will contain numbers or letters in a sequence of 1v2 (no space) 3v4 etc. up to 11v12 or a combination of these numbers.


    This formula works for a single number =IF(G5="","",INDEX($L$5:$L$8,MATCH($G5,$K$5:$K$8,0)))


    but not what I am after.


    Many thanks for any help that you can give me.



    [COLOR="#0000CD"]MODERATOR EDIT
    Please do not use code tags with formula - - these are reserved for use with VBA code only.
    Code tags removed. Thanks.[/COLOR]

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


    Can you give an example or two of what exactly you would match? So instead of $G5 what are you looking for?

    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


    Many thanks for looking, it's to match another number. in other words if G5 contains data of 1v2 and if any cell between K5:K8 contains either a 1 or a 2 then there is a match and a name in L5:L8 is copied over.


    Hope that gives a fuller explanation as I didn't want to post a worksheet as it's a bit of a "working" mess.

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


    Is it always a "v" in between?


    If so, try:


    =IF(G5="","",INDEX($L$5:$L$8,IFERROR(MATCH(LEFT($G5,FIND("v",G5)-1)+0,$K$5:$K$8,0),MATCH(MID($G5,FIND("v",G5)+1,2)+0,$K$5:$K$8,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


    Brilliant, works perfectly, as always many thanks to you folks who spend so much time helping us. Regards

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


    From PM:



    Perhaps try:


    =IF(G5="","",IFERROR(IF(LEFT(G5)="*","*","")&INDEX($L$5:$L$8,MATCH(LEFT(SUBSTITUTE(G5,"*",""),FIND("v",SUBSTITUTE(G5,"*",""))-1)+0,$K$5:$K$8,0)),INDEX($L$5:$L$8,MATCH(MID(SUBSTITUTE($G5,"*",""),FIND("v",SUBSTITUTE(G5,"*",""))+1,2)+0,$K$5:$K$8,0))&IF(RIGHT(G5)="*","*","")))

    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


    Sorry to bother you again but your formula in G5 works perfectly:-


    IF(G5="","",IFERROR(IF(LEFT(G5)="*","*","")&INDEX($L$5:$L$8,MATCH(LEFT(SUBSTITUTE(G5,"*",""),FIND("v",SUBSTITUTE(G5,"*",""))-1)+0,$K$5:$K$8,0)),INDEX($L$5:$L$8,MATCH(MID(SUBSTITUTE($G5,"*",""),FIND("v",SUBSTITUTE(G5,"*",""))+1,2)+0,$K$5:$K$8,0))&IF(RIGHT(G5)="*","*","")))


    but when I copy down to G6 and G7 like so, only changing the G5 to G6 & G7 etc:-


    IF(G6="","",IFERROR(IF(LEFT(G6)="*","*","")&INDEX($L$5:$L$8,MATCH(LEFT(SUBSTITUTE(G6,"*",""),FIND("v",SUBSTITUTE(G5,"*",""))-1)+0,$K$5:$K$8,0)),INDEX($L$5:$L$8,MATCH(MID(SUBSTITUTE($G6,"*",""),FIND("v",SUBSTITUTE(G6,"*",""))+1,2)+0,$K$5:$K$8,0))&IF(RIGHT(G6)="*","*","")))


    It doesn’t work and gives the value error, what am I doing wrong? Regards

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


    Can you please post a sample workbook. I fear there might be simpler methods to attain your goal.

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



    Hope this G5 (bold) is a typo.

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


    Yes it is I thought I had gone in later and edited it out but I guess not, sorry. OK will upload a workbook

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


    I don't understand the issue. Can you elaborate?

    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


    Sorry, apologies you are correct the test app is OK and there are no issues there. I extracted this test workbook from a much larger workbook in order to reproduce the problem but unfortunately it is too big to be uploaded and I would need to re-create the problem section.


    I am beginning to think that the $K$5:$K$8,0)) part (which isn't the actual cells used in the original W/Bk, as they are Y14:Y25) is where the problem might lie as Y14 has a "1" in it but Y13:Y25 have numbers from 2 to 12 which were copied over by a formula, if you follow me. In other words is there any difference between an actual number or one produced by a formula?


    What I will need to do is try and re-create the problem in a smaller W/Bk so thanks for your help and will get back to you when I have.

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


    Quote

    In other words is there any difference between an actual number or one produced by a formula?


    It should not make a difference as long as they are all the same format (Number). My formula assumes the data in K5:K8 are numbers formatted as Number (General). If you take out the instances of +0 in my formula, then it would be assuming K5:K8 are numbers formatted as Text.... so it depends on how you enter the numbers in K5:K8 and what your actual formulas in K5:K8 are.

    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


    Great :)


    You did not give any problems. We are here to help. ;)

    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


    I am so very sorry to bother you again but something weird is happening in that when I use this formula to match 11:


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


    on the right hand side of say 1v11 (no spaces) to pick up the data represented by 11 in fact it picks up the data of 1 instead This only happens on some numbers to the right hand side and if I put in 1 (space) v (space) 11 it works. It seems to mix up the numbers 1, 2, 11 and 12 but is OK between 3 and 9. I can try and set it up in a “smaller” test W/Bk to let you see unless you can figure out what is happening or have I input an incorrect formula? I use excel 2007.


    Regards

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


    Looks like you made changes in the formula... are you checking right side of the "v" first now?

    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 think I wonder if I have mislead you with my initial request


    OK I have in say cell E113 *1v2 and I want the left hand number 1 to index and match the same number Y14 between Y14:Y25 and the matching name in Z14 between Z14:Z25 and this name to appear in say D134 and the right hand number 2 to copy a matching name and copy it to D136 so that D134 would have the matching name for (1) and D136 would have the matching name for (2).


    I assumed that I would have to reverse the formula for the right hand name (the left hand matching name is fine) and it seemed to work for most of the numbers apart from these few mentioned above.


    I guess me reversing the “Right/Left” part of the formula was incorrect and wonder what the correct formula now is for the right hand side of 1v2 ? If I have explained it properly.