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.
dscheikey no need to get excited. I'm very busy and have a deadline to reach. I posted what worked so that should help.
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.
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.