MATCH Function -> #N/A, #VALUE!, 1

[sarsteg]

I have a project in which I am needing to determine if a form has yet to be completed. On one tab, I have a list of all assigned ID codes (referred to as 'All' tab). On another, I have a large running-list of the completed ID codes thus far (referred to as 'Dataset' tab).


I have used this formula within a table on the All tab: =MATCH([@[ID Code]], Dataset[@ID Code], 0)


The result is a mixture of #N/A (total of 727 items), #VALUE! (total of 2488 items), and 1 (only 1 item). Both columns are in number formatting.


How can I mark which forms have been completed thus far with in my table on the All tab?


Comments and suggestions are welcome!
Thank you,
Sarah

 

[elmer007]

Is your list of complete ID codes just a plain list, such that if the ID code appears on the "Dataset" tab then it's complete (and if it's not there then it's not complete)?

 

[sarsteg]

The assigned ID codes ('All' tab) has all the codes which we are expecting to receive. The completed ID codes ('Dataset' tab) list the forms that have been completed to date. ID codes are added to the 'Dataset' tab as they are completed, but the 'All' tab list both completed and incompleted ID codes.

Both are "plain text" - not formula generated. Both columns are numbers, stored in number formatting. I have also tried changing them both to text formatting without success. I would like to keep track of which ID codes have been completed thus far in the project by comparing the two lists.

I hope I am explaining this sufficiently, but please let me know if I should elaborate a bit more.

Thank you for your help(!)

 

[elmer007]

I think I understand. I don't use tables much (which you appear to have based on the "@" in the formulas, which is fine), so I'm going to write my formula for just plain cells. It should still work in the table:
I'm assuming that the ID codes are in column A on both tabs.
In the cell that you want to indicate the completion of the code, put: =IF(ISNUMBER(VLOOKUP(A2,Dataset!$A:$A,1,FALSE)),"Complete","Incomplete")

If your ID codes are in a different column on the "All" tab, then change the "A2" to whichever column they're in. If your ID codes are in a different column on the "Dataset" tab, then change the "$A:$A" to whichever column they're in.

Let me know if this doesn't work or if you have any questions.

 

[elmer007]

Actually, a better one would be: =IF(ISNA(VLOOKUP(A2,Dataset!$A:$A,1,FALSE)),"Incomplete","Complete")
This should handle IDs that aren't just numbers, whereas the above post is limited to numbers.

 

[sarsteg]

Hmm... the formula shows that only one form has been completed (728 have been completed).

Here is a link to an example file: https://www.dropbox.com/s/xcj2noxv01vpeml/ID Codes.xlsx?dl=0

What do you think could being going on here?

 

[elmer007]

I can't access the file from the computer that I'm on, but some possible issues are the "identical-ness" of the ID codes. Spaces, typos, etc. can cause problems. Are some of the IDs hiding spaces on the front or back (e.g., "12345 " instead of "12345")?

 

[sarsteg]

I tried the formula: =IF(ISNA(VLOOKUP(A2,Dataset!$A:$A,1,FALSE)),"Incomplete","Complete"). The result was 3168 items marked as Complete, much more than we have completed.

I conducted search for spaces, but did not find any in either column. I am not sure what could be causing the formula to not work as expected.

 

[elmer007]

That is odd. It works in my test spreadsheet, and I use it in my own files as well...
I'm at a disadvantage since I can't use Dropbox, but maybe explaining how the formula works could help you identify where an issue may be:

The IF formula will return "Incomplete" if the first argument is TRUE, and "Complete" if the first argument is FALSE.
The ISNA formula checks to see if what's inside it returns #N/A. If it does, it returns TRUE. If not, it returns FALSE.
The VLOOKUP will return #N/A if the value it's looking for (the ID code) is not in the range is looking to. So, if it looks for ID 12345, and 12345 is not in the ID column of the Dataset tab, then it returns #N/A.

When the VLOOKUP returns the #N/A, the ISNA formula returns TRUE, which causes the IF formula to return "Incomplete".

On the other hand, if the VLOOKUP finds what it's looking for, then it does not return #N/A. So, the ISNA formula returns FALSE, which causes the IF formula to return "Complete".


The main things that I could see causing a problem are:
1. The "A2" in the formula needs to go to the cell on the All tab that has the ID code
2. The "$A:$A" in the formula needs to go to the column on the Dataset tab that has the ID codes
3. The ID code on the All tab has to match exactly to the code on the dataset tab (maybe try resetting the formatting? you mentioned that you had tried changing between number/text- maybe it's flipped?)

Are there any duplicate ID codes listed?

 

[Aladin Akyurek]

=ISNUMBER(MATCH([@[ALL ID Codes]],Dataset[Completed ID Codes],0))+0

1 means a hit, 0 a miss. If you want to, custom format the formula cell as:

[=0]"Incomplete";"Complete"