Vlookup

[Killer17]

I need some assistants please

Worksheet consist of sheets W1 to W32 and a Weekly sheet

In each work Sheet there are names listed from B8:B11

In cells D8: F11 are scores

what I’m trying to do in my “Weekly Score Sheet” is list the top score that the person achieved for that week out of three scores submitted.

I have created an example of what I’m trying to achieve

Thanks ahead!

[solnajeff]

Hi

If you use the MAX function that will show the high score for each week, using as follows

Please Login or Register  to view this content.

.

I have changed your example to reflect this.

Regards

jeff

[snasui]

Try this,

Sheet “Weekly Score Sheet”

D5

Please Login or Register  to view this content.

Copy across then down.

Hope this helps.

[sglife]

I think this is what you need

Please Login or Register  to view this content.

then copy down

[Killer17]

Thanks guys but I guess I should have told you that the

"Weekly Sheet" in not in the same workbook as the W1 sheets

Workbook 1 has Weekly Top Scores Sheet
Workbook 2 Named "Weekly Sheets" Has W1 :W32 Sheet

I tired this but I keep getting an N/A Error

=MAX('[Weekly Sheets.xlsx]W1'!$D$7,MATCH($A6,'[Weekly Sheets.xlsx]W1'!$B$8:$B$197,0),0,1,3))

[snasui]

Try this,

Please Login or Register  to view this content.

[Killer17]

Try this,

Please Login or Register  to view this content.

Hey thanks snasui that seems to work very well.....

The only problem I'm seeing if there no name or number under the W1:W32 sheets it's returing #N/A in the Weekly Sheets.

Is there away if there's not match that the cell stays blank Not #N/A

[dcgrove]

Try this:

=if(MAX(INDEX('[Weekly Sheets.xlsx]W1'!$D$8:$F$197,MATCH($A6,'[Weekly Sheets.xlsx]W1'!$B$8:$B$197,0),0)),"",MAX(INDEX('[Weekly Sheets.xlsx]W1'!$D$8:$F$197,MATCH($A6,'[Weekly Sheets.xlsx]W1'!$B$8:$B$197,0),0)))


You may need to put some parentheses in there to make it work, but Excel should let you know if you do.


Clayton

[Killer17]

Try this:

=if(MAX(INDEX('[Weekly Sheets.xlsx]W1'!$D$8:$F$197,MATCH($A6,'[Weekly Sheets.xlsx]W1'!$B$8:$B$197,0),0)),"",MAX(INDEX('[Weekly Sheets.xlsx]W1'!$D$8:$F$197,MATCH($A6,'[Weekly Sheets.xlsx]W1'!$B$8:$B$197,0),0)))


You may need to put some parentheses in there to make it work, but Excel should let you know if you do.


Clayton

Hey dcgrove

This formula isn't working.... It's removing scores if there's a name or number.
All I want if there's no name of number to leave the cell blank

[ratcat]

G'day

Try this,

Please Login or Register  to view this content.

You may need to put some parentheses in there to make it work, but Excel should let you know if you do.

HTH

[Killer17]

Thanks guys for your help