• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Check if numbers/ items are in the list& identify the missing number/ item

U

Ufoo

Member
Hello excel experts,

Suppose I want to find if a list of number in a column is complete. I thank chandoo.org for teaching me this formula AND(COUNTIFS(A4:A11,ROW(INDIRECT("1:"&5)))=1). One can also use this formula to identify the missing number in the list: (LARGE(--ISNA(MATCH(ROW(INDIRECT("1:"&5)),A4:A11,0))*ROW(INDIRECT("1:"&5)),ROW(1:1))). It is also possible to hardcore the array.

However, while it is possible to identify if a list of text is complete by using the same approach, identifying the word which is missing has been tough. Someone has come up with this formula, but it is returning the first name in the list; not the missing name: =IFERROR(INDEX(SourceRange,SMALL(IF(ISNA(MATCH(SourceRange,RefRange,0)),ROW(SourceRange)-ROW(SourceRange)+1,99^99),ROW(1:1))),""). Any ideas please?
 
Hi ,

The correct formula would be :

=INDEX(RefRange, SMALL(IF(ISNA(MATCH(RefRange,SourceRange,0)), ROW(RefRange) - MIN(ROW(RefRange)) + 1),ROWS(A$1:A1)))

entered as an array formula , using CTRL SHIFT ENTER. Enter this in any cell , and copy down.

RefRange is the range containing the reference words , the master list as it were , and SourceRange is the range containing the input words which needs to be checked for matches.

Narayan
 
You must log in or register to reply here.
Back
Top