#Name error in Let() variable name

[Cumberground]

I'm trying to convert a working formula to use Let() in order to simplify things but I'm getting #Name error for the name1 variable.
Old:

=IFERROR(IF($AL6="","",INDEX('Scoresheet 1'!$CF$34:$CF$57,MATCH($AL6,'Scoresheet 1'!$CA$34:$CA$57,0))),"")

New:

=LET(vScores,'Scoresheet 1'!$CF$34:$CF$57,IFERROR(IF($AL6="","",INDEX(vScores,MATCH($AL6,vScores,0))),""))

The formula looks-up the player name from cell $AL6 and returns the index/matched score from another worksheet - 'Scoresheet 1'!$CA$34:$CA$57

It works perfectly on the old formula but won't work on the new, it fails even if I (rather redundantly) do the following:

=LET(vScores,'Scoresheet 1'!$CF$34:$CF$57,IFERROR(IF($AL6="","",INDEX('Scoresheet 1'!$CF$34:$CF$57,MATCH($AL6,'Scoresheet 1'!$CF$34:$CF$57,0))),""))

The array from Scoresheet 1 is returned correctly as {53;47;43;41;40;39;36;36;36;35;34;32;31;31;31;30;29;28;28;27;27;23;22;19} in all three formula
Any ideas why it doesn't like the name1 variable?
I've tried using different variable names just in case...

Thanks

 

[RoryA]

but I'm getting #Name error for the name1 variable

What name1 variable? That's not in the formula anywhere. The formula you posted works fine for me.

 

[Fluff]

The formula shouldn't return #NAME but #N/A as you are trying to match AL6 with col CF rather than col CA

 

[Cumberground]

Ah, thanks Fluff, rooky mistake.
I was so keen to use Let() I forgot to check I was using the correct columns

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[jkpieterse]

Doesn't this produce the same:

=IF(ISERROR(MATCH($AL6,'Scoresheet 1'!$CF$34:$CF$57,0)),"",$AL6)

 

[Peter_SSs]

Doesn't this produce the same:

=IF(ISERROR(MATCH($AL6,'Scoresheet 1'!$CF$34:$CF$57,0)),"",$AL6)

I think that you missed that the old (working) formula was looking for $AL6 in column CA & the problem is that the OP's new formulas (& yours) are looking for it in column CF, as @Fluff pointed out in post #3