INDEX and MATCH and LEFT?

[Sean15]

Hi:

Tab1: Mike Sales & Service $4,500.00

Tab2: Mike Sales & Service (MSS)

I want to get value from tab 1 for Mike Sales. Value is in column F. I cannot change name in tab 2.

Formula below is returning #N/A.

INDEX('tab1'!$F$5:$F$31,MATCH(LEFT(A3,10),'tab1'!$E$5:$E$31,0))

Could you suggest a fix please?

Regards,
Sean

 

[RoryA]

You need to add a wildcard:

INDEX('tab1'!$F$5:$F$31,MATCH(LEFT(A3,10)&"*",'tab1'!$E$5:$E$31,0))

 

[Sean15]

Thank you very much.

Regards,
Sean