lookup or match with left & right

[micope21]

Hi
Here example file. I have try lot of formula to work with. Nothing solved. Anyone got any idea?

[khamilton]

Well i found a solution in 2007 but it will not work in 2003. So I'm trying to find a 2003 solution.

[micope21]

Well i found a solution in 2007 but it will not work in 2003. So I'm trying to find a 2003 solution.

Thank you for reply. I'm sorry I got excel 2010 and forgot to change my detail. Yes I agreed won't work with 2003.

[khamilton]

well then try this monster.


Example 1.xlsx

[Haseeb Avarakkan]

Hello micop21;

Use this Array Formula in D3 & copy across & down.

=IFERROR(TRIM(SUBSTITUTE(SUBSTITUTE(LOWER(INDEX(D$16:D$21,MATCH("* "&$B3&" *"," "&D$16:D$21&" ",0))&" v"),$B3&" v",""),"v","")),"")

See the attached.

[khamilton]

@ haseeb Just an FYI some columns on your example have multiple number 1's

[khamilton]

sorry i don't now where my attachment went. i will reattach.

[khamilton]

Okay this should be correct.

Copy of Example1.xlsx

[micope21]

Thank you very much khamilton & Haseeb A . I copy both to League stats file and it work.

Haseeb A: There 2 missing number 1 which should be 11 and I put in =IF($AA$25="x","",IF(VLOOKUP(E62,$B$62:$C$73,2,FALSE)<>"",VLOOKUP(E62,$B$62:$C$73,2,FALSE))) for teams names. It come up all N/A.

khamilton: Your spot on. It work with =IF($AA$25="x","",IF(VLOOKUP(E62,$B$62:$C$73,2,FALSE)<>"",VLOOKUP(E62,$B$62:$C$73,2,FALSE))) Got all teams names.

I didn't realise need to work with {=formula} I was working without it that why it didn't work at all. I been with excel 2003 for long time till i brought new excel 2010 month ago. That something i need to learn to work on it. Thank you for your time. Problem solved.

Cheers.

[micope21]

aaaaaaaaaaa