Compare

[CribbsStyle]

Ok This is the setup.....

A24
--------------------------------------------------------
George Shirley

Range
--------------------------------------------------------
HiddenStats!A20:A150

Format of Names in Range
--------------------------------------------------------
G. Shirley

Code
--------------------------------------------------------
=INDEX(HiddenStats!$A$20:$N$150,MATCH(A24,HiddenStats!$A$20:$A$150,FALSE),7)


I need it to recognise "G.Shirley" as George Shirley, is there a way?
Any help would be appreciated!

 

[RagDyeR]

Try this:

=INDEX(HiddenStats!$A$20:$N$150,MATCH(LEFT(A24)&"."&MID(A24,FIND("
",A24),50),HiddenStats!$A$20:$A$150,FALSE),7)

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Ok This is the setup.....

A24
--------------------------------------------------------
George Shirley

Range
--------------------------------------------------------
HiddenStats!A20:A150

Format of Names in Range
--------------------------------------------------------
G. Shirley

Code
--------------------------------------------------------
=INDEX(HiddenStats!$A$20:$N$150,MATCH(A24,HiddenStats!$A$20:$A$150,FALSE),7)


I need it to recognise "G.Shirley" as George Shirley, is there a way?
Any help would be appreciated!

 

[Biff]

Use a helper cell to parse the name:

A24 = George Shirley

A25 = formula:

=LEFT(A24)&". "&MID(A1,FIND(" ",A24)+1,255)

Returns: G. Shirley

Then:

..........MATCH(A25,HiddenStats!$A$20:$A$150,FALSE)........

Or:

..........MATCH(LEFT(A24)&". "&MID(A1,FIND("
",A24)+1,255),HiddenStats!$A$20:$A$150,FALSE)........

Biff

 

[CribbsStyle]

Thanks for the help guys! I combined what u both said into this and it
works perfectly!

=INDEX(HiddenStats!$A$20:$N$150,MATCH(LEFT(A24)&". "&MID(A24,FIND("
",A24)+1,255),HiddenStats!$A$20:$A$150,FALSE),7)

Dennis

 

[CribbsStyle]

One more question, is there a way to have the cells not display #VALUE
when A25 is blank?

Dennis

 

[RagDyeR]

Try this:

=IF(A24<>"",INDEX(HiddenStats!$A$20:$N$150,MATCH(LEFT(A24)&"."&MID(A24,FIND("
",A24),50),HiddenStats!$A$20:$A$150,FALSE),7),"")

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

One more question, is there a way to have the cells not display #VALUE
when A25 is blank?

Dennis

 

[Biff]

is there a way to have the cells not display #VALUE

when A25 is blank?

Try this. It will leave the cell blank:

=IF(A25="","",your_formula_here))

Biff

One more question, is there a way to have the cells not display #VALUE
when A25 is blank?

Dennis

 

[Biff]

is there a way to have the cells not display #VALUE

when A25 is blank?

Try this. It will leave the cell blank:

=IF(A25="","",your_formula_here))

Biff

One more question, is there a way to have the cells not display #VALUE
when A25 is blank?

Dennis

 

[CribbsStyle]

Thanks, but I figured it out, I used this...

=IF(ISERROR(INDEX(HiddenStats!$A$20:$N$148,MATCH(LEFT(A24)&".
"&MID(A24,FIND("
",A24)+1,255),HiddenStats!$A$20:$A$148,FALSE),2)),"",INDEX(HiddenStats!$A$20:$N$148,MATCH(LEFT(A24)&".
"&MID(A24,FIND(" ",A24)+1,255),HiddenStats!$A$20:$A$148,FALSE),2))

For cell A24 of course, not A25

Try this:

=IF(A24<>"",INDEX(HiddenStats!$A$20:$N$150,MATCH(LEFT(A24)&"."&MID(A24,FIND­("
",A24),50),HiddenStats!$A$20:$A$150,FALSE),7),"")

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

One more question, is there a way to have the cells not display #VALUE
when A25 is blank?

Dennis

Use a helper cell to parse the name:

A24 = George Shirley

A25 = formula:

=LEFT(A24)&". "&MID(A1,FIND(" ",A24)+1,255)

Returns: G. Shirley

Then:

.........MATCH(A25,HiddenStats!$A$20:$A$150,FALSE)........

Or:

.........MATCH(LEFT(A24)&". "&MID(A1,FIND("
",A24)+1,255),HiddenStats!$A$20:$A$150,FALSE)........

Biff

messagenews:[email protected]...