Forum Discussion
Formula to reveal the row number of where the duplicate value is located
Thank you. This is a perfect match =IFNA("B"&XMATCH(A2,B:B),"")
="B"&MATCH(1,COUNTIF(A:A,B:B),0) keeps having me reboot Excel due to a delay, not sure what the reason is. I'll go with the one above.
="B"&MATCH(A2,B:B,0)
My dear! On a community site, we all want to learn from each other. Users who follow us here want to understand which is the right formula for the problem you have described. Find a duplicate entry from column A and column B. The formula should bring the cell in column B where the duplicate entry is to be found.
I have created a formula with MATCH() and COUNTIF() that works in Microsoft 365 and Excel for the web. The formula does not work for you. To find out more precisely why the formula does not work for you, I have asked you to upload a screenshot here. Other users should learn under which circumstances the formula does not work.
Unfortunately, you don't seem to be willing to participate in the troubleshooting. On the one hand, you want help from the community. On the other hand, you are not prepared to give something back to the community.
I therefore ask you again to help with the clarification.
Thank you. This is a perfect match =IFNA("B"&XMATCH(A2,B:B),"")
="B"&MATCH(1,COUNTIF(A:A,B:B),0) keeps having me reboot Excel due to a delay, not sure what the reason is. I'll go with the one above.Good that you have now shown the screenshot here. Now I've realised that I misinterpreted your question. I understood that you want to find the (singular) duplicate value that occurs in both column A and column B. From the image I interpret that there are several duplicate entries and you want to find the duplicate value in column B for each value in column A. This was not clear from your description and questions.
The correct way is to write the formula in cell A2 and then copy it down so that each row refers to the cell in column A.
The formula=IFNA("B"&XMATCH(A2,B:B),"")is the right one for this task.
Please note that only the first hit in column B is shown.What I find very irritating is your first screenshot with the "0" as the result. As already mentioned, this cannot be correct. Since the formula starts with "B"&, the result must be a text also start with "B". The only way to achieve the 0 there would be if the formatting of this cell is user-defined and the format function says something like 0,0,0,"0". Then there would always be a 0 in this cell when there should actually be a text there. Can you check whether the formatting of the cell is user-defined?
According to your screenshot, B1 should actually be the result there, as your headings in columns A and B are already identical.
- In my case, the N/A refers to "Not Found", no duplicate. Copilot suggested I use this formula: =IFNA(VLOOKUP(A49, B:B, 1, FALSE), "Not Found")
dscheikey Ok I have a few minutes, here's what I get. On the top is the formula you suggested where it gives me a "0" return. On the one I posted, I also receive the "N/A" on some of them.
I'm doing something wrong because the formula that works for you gives me a #N/A error.
It would be nice if you could spare some time again and provide some clarification.
dscheikey no need to get excited. I'm very busy and have a deadline to reach. I posted what worked so that should help.