Formula to pick character based on name and date

Dear all,

i have attached table for staff attendance.

could anyone help me to provide a formula as per my requirement in sheet attached.

all yellow highlighted is my requirement.. thanks

thanks in advance.

Which version of Excel are you using?

Please update your forum profile to Excel 2019. Do this NOW, please.

Thanks.

=SUMPRODUCT(($A$3:$A$6=$J3)*($B$3:$H$6=R$2))
Try this formula

That doesn't address the whole problem. It only works for the last three columns - the OP needs everything in the yellow section.

K3 copied down.

Formula:

Please Login or Register  to view this content.

Might need to be Array Entered.

I changed TMS formula a little
In K3 put formula =INDEX(B$3:B$6,MATCH($J3,$A$3:$A$6,0))&""
Drag the formula down to K5
Drag the formulas right to column Q
In R3 put formula =COUNTIF(K3:Q3,R$2)
Drag formula right and down

Correct the formula in R3 to =COUNTIF($K3:$Q3,R$2) in attached file

/LGS

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

@LARS GULYAS:

Not entirely sure why you would do this:

I changed TMS formula a little
In K3 put formula =INDEX(B$3:B$6,MATCH($J3,$A$3:$A$6,0))&""
Drag the formula down to K5
Drag the formulas right to column Q

This:

Formula:

Please Login or Register  to view this content.

should be:

Formula:

Please Login or Register  to view this content.

@LGS:

It was OK in column S, T

Not if you put that formula in cell R3 and drag it across and down . . .

DEAR AliGW AND TMS AND LARS GULYAS & popipipo ,

Thanks all of you 4 guys for your efforts for my questions. sorry i was away from office because of death of our King of Kuwait on Saturday.

the formula given by LARS GULYAS and modified by TMS for R2:T5 Formula exactly what i was looking for.. thanks it work perfectly.

However, i wish if i change any of 2 ranges .. i.e. date range K:2 to Q2 and if I change name range A3:A5, i should get the same data from the range A2:H6.. is it possible?

thanks in advance

You're welcome.

Please upload a new file with the change in layout/requirements.

tariqnaz2005

Put this Formula in K3
=VLOOKUP($J3,$A$2:$H$6,MATCH(K$2,$A$2:$H$2,0),0)&""
Drag down to K5 and right to Q5.
Erace J11
I often use this formula in tables to sort in the right order,
e.g. football, ice-hockey, handboll tables to see the current standing

Name and Date can be arranged as you like (any way)

Hope it takes care of your question

/LGS

@tariqnaz2005: You're welcome. Thanks for the rep.