I have a sheet filled with different data which is classified by years (Part1 in the attached file). now I need to classify them by Names (Part2).
How can I use the Vlookup for different "Name" so that it returns the year? (/to return a checkmark when the name exists in a certain year)
or is there a better function?
Please try at
B21
=SUMIFS($B$6:$J$14,$A$6:$I$14,A21)
C21:F21
=IF(COUNTIF(INDEX($A$5:$K$14,,MATCH(C$19,$A$3:$K$3,)),$A21),"✅","")
I tried this and it doesn't return anything.
I only changed the $A$5:$K$14 to 'sheet2'!$A$5:$K$14
Last edited by AliGW; 05-11-2020 at 08:22 AM. Reason: Please don't quote unnecessarily!
Try these, then:
B21
=SUMIFS($B$6:$J$14,'Sheet2'!$A$6:$I$14,A21)
C21:F21
=IF(COUNTIF(INDEX(Sheet2'!$A$5:$K$14,,MATCH(C$19,Sheet2'!$A$3:$K$3,)),$A21),"✅","")
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
I selected the table while writing the formula and this is how it looks:
=IF(COUNTIF(INDEX('Sheet2'!$J$5:$EN$192;;MATCH(H$4;'Sheet2'!$J$2:$EN$2; ));$D7);"ü";"")
(H$4=C$19, $D7=$A21, "ü" from symbols)
Last edited by (T_T); 05-11-2020 at 09:09 AM.
Guten Tag!
The umlauted 'u' (ü) returns a tick in the Wingdings font.
Do you still need help with this? It's not clear from your last post.
Guten Tag! ^_^
Yes, the formula still doesn't return anything.
sorry my last reply wasn't clear. I wrote it to see if there is a problem there.
I tried COUNTIF(INDEX('Sheet2'!$J$5:$EN$192;;MATCH(H$4;'Sheet2'!$J$2:$EN$2; ));$D7 and it returns 0, where it should be 1. But I still can't figure out the problem
Last edited by (T_T); 05-12-2020 at 05:45 AM.
Thank you both!
I found the problem and it works now
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks