# 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 &amp; 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 &amp; 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 &amp; 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 &amp; 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 &amp; 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 &amp; Match formula for a number in a string

Sorry for all this and I attach sample file which hopefully shows what I wold like.

## Files

• Re: Index &amp; 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 &amp; 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!