Formula help

[bluenose5709]

Hi,

I hope that there are some magicians out there who may wave their magic wand and assist me with my conundrum

I am focusing upon two worksheets in the same workbook

sheet 1 (Attendance)

This has a list of names starting in B5 (populated from a list), and team name in C5, (two teams = Blue, Red) Followed by many columns for each moth (we meet twice weekly so there is a columns per meeting) in order to record attendance.

In cell DC5 I show the attendance percentage for Jan, DD5 = Feb, DE5 = Mar, etc…

Sheet 2 (blue team points). (This is where I need the magic)

So I have my names in column B, starting in cell B5. populated from a list filtered to show only those in Blue Team

I would like a formula (in cell D5) that will look at sheet one, “attendance” cell “DC5” match the name in sheet “blue_team points” cell “B5”and populate the matching results from “attendance” results into cell D5


I have unsuccessfully tried INDEX & MATCH but cannot seem to get the magic answer. One I have it sussed for one cell, I shall then do the same for all 12 months and also for all names in my list.

Thank you all in advance and I hope my explanation does this justice.

Regards
Arran

 

[zero269]

I can't see what your data looks like, but have you tried using XLOOKUP instead? Just curious...

 

[bluenose5709]

Zero269,

Thank you. I shall attempt that latter today and come back to you.

Using index and match I do get data but it shows the results for smith, instead of for jones.

 

[bluenose5709]

Here are some screenshots of what i am trying to achieve:

Sheet one records attendance with a percentage for Smith (Cell B5) recorded, for Dec in Cell DO5

this is Sheet 2 and what i am trying to achieve is for the formula in cell N3 to look at sheet one, confirm it is the row that matches the person (Sheet One Liam Smith, Sheet two Liam Smith) and then pull the correct cell from sheet one Column DO to Sheet two.

The names are populated from a List which is in a different worksheet. and the two teams here are called Reaper & Poseidon.

Whilst the Attendance (Sheet one) record covers everyone, Sheet two will pull information that is specific to Reaper only (i will create another sheet once this is working for Poseidon) Names may change over time so i need this to automatically show the information that is correct and relevant to who is in what team, hence i am trying to get the fomula to match the details.

thank you

 

[bluenose5709]

I can't see what your data looks like, but have you tried using XLOOKUP instead? Just curious...

Zero269,

Have been playing with XLOOKUP but can't make that work either

thank you

 

[bluenose5709]

Upon reading the original post i thought my question was not clear / confusing, so add a clarifying post here as a response to my own question.

I am trying to Match data and pull data from one sheet to another.

Worksheet "Attendance_Input" calculates percentage attendance across the row. The example below shows the December attendance for Liam Smith as a percentage in Cel "DO5"

(You will notice in column C below, there are currently two possible options but this may change)

In my next worksheet "Reaper_Points_List" i am trying to INDEX and MATCH the data in worksheet Attendance in order to populate the Monthly attendance figures. (this worksheet only list those names in the Reaper team, there is another worksheet for Poseidon, hence the attempt to match)

the formula that i am attempting to use for December in is:

=IF(B3="","",IF(Attendance_Input!N5="Reaper",INDEX(Statistics,MATCH(Attendance_Input!M5,Reaper_Name,0))))

I have tried variations on this formula but none have i got to work properly. you will see that in Column C below, it is pulling data, but this does not match the named person...

The names in both worksheets are populated from a named List which is in a different worksheet. (Screenshot below) The two teams here are called Reaper & Poseidon. This list may change and if it does the entries on the two Worksheets listed above will change also, hence trying to get the formula to match the name and populate the correct data.

Here is hoping that someone may educate me in the error of my ways.

Thank you