Excel Magic Trick 1377

Lookup Formula To List All Incomplete Assignments: INDEX, TEXTJOIN & More

DESCRIPTION

See how to create a formula to list incomplete / missing student assignments:
(00:11) Introduction
(01:14) Formula to count missing assignments for a particular student using: 1) INDEX & MATCH functions to lookup a whole row (row for student) and then use that range in COUNTIFS function
(04:25) Formula to retrieve incomplete assignments from Column Headers in a Two-Way Lookup Table where Student Names make up the Row Headers and the inside part of the table has “Y” and “N” for each assignment. See the functions IF, ROWS, INDEX, AGGREGATE, COLUMN, MATCH and ROWS in this array formula.
(13:38) Formula to horizontally list a Particular Student’s Incomplete Assignments in a single cell using INDEX, MATCH and IF Functions in an array formula that we put inside the special “Office 365, Excel 2016, Insider Program” function TEXTJOIN.
(17:09) Formula to vertically list a Particular Student’s Incomplete Assignments in a single cell using INDEX, MATCH, TEXTJOIN and CHAR functions.
(18:21) Summary
{"ft_lang_label":"__label__en","ft_lang_prob":0.74430084,"math_prob":0.9195937,"size":1180,"snap":"2019-43-2019-47","text_gpt3_token_len":288,"char_repetition_ratio":0.13180272,"word_repetition_ratio":0.08791209,"special_character_ratio":0.25169492,"punctuation_ratio":0.108597286,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.99446654,"pos_list":[0],"im_url_duplicate_count":[null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2019-10-18T06:32:55Z\",\"WARC-Record-ID\":\"<urn:uuid:1fdba1c1-8d74-47fc-a82b-36096591d717>\",\"Content-Length\":\"74910\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:a617fcc2-5ff3-4ef0-9e14-8374dd1366e2>\",\"WARC-Concurrent-To\":\"<urn:uuid:270ec85e-b12b-42da-9047-c12f91c7d21c>\",\"WARC-IP-Address\":\"185.11.240.163\",\"WARC-Target-URI\":\"http://masterexcel.net/excel-magic-trick-1377/\",\"WARC-Payload-Digest\":\"sha1:VCIJY4XZMNLPNOT5U6NBKAVAEBSJ5ITF\",\"WARC-Block-Digest\":\"sha1:NRNP5ADXK2AG2I6MQONF27LWATLS2XFS\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2019/CC-MAIN-2019-43/CC-MAIN-2019-43_segments_1570986677964.40_warc_CC-MAIN-20191018055014-20191018082514-00481.warc.gz\"}"}