Is the order of the column comparison considered?
I have a whole bunch of ids in columns like this:
A B C
id234 id002
id294 id103 2
id700 id023
id201 id294
id892 id500
id932 id294
id600 id100
id402 id031
id725 id802
id702 id031
id435 id799
id031 id311 0
id007 id846
id111
id027
My idea is to use the COUNTIFS function in Excel to count how many times an ID from column "A" appears in column "B" and displays the output in column "C". So, in the first cell of column "C" =COUNTIFS($B1:$B13,A1)
. It works as expected, for example id294
. (It finds two matches in column "B" and prints 2
). The same should happen for id031
. Column "B" has a couple of matches, so it should print as well 2
. Instead, it prints 0
.
I suspect this is because since the COUNTIFS function finds id294
first in column "A" and their two matches in column B "does the job, but as it finds id031
in column A after they appear in column "B", it doesn't count correctly and prints 0
out as if no match was found. Is there an order here? How to fix it?
Also, when I use the function =IF(ISNA(VLOOKUP(A1,$B1:$B13,1,FALSE)),"No","Yes")
to print "Yes" or "No" depending on whether there is a match or not, it prints Yes
if the value is in column "A" first, but prints No
if the value is in column "B" first ...
source to share
Please bind the start of the range B
(i.e. $B$1
, not $B1
). Currently, when you copy, $ B1 goes to $ B2, and so on, and the search range gradually decreases in size (in fact - it remains the same number of cells, but row by row, the cell is at the top, with the value being replaced by an empty cell at the bottom) ... The entire range "slides" down. Try:
=COUNTIFS($B$1:$B13,A1)
Or, since in your example "not found" appears as a space:
=IF(COUNTIFS($B$1:$B13,A1)>0,COUNTIFS($B$1:$B13,A1),"")
source to share