It should be simple, but when I ask on other Excel forums I get overly complicated answers, that don't work.
Basically, I have one lists of serial numbers, list 1 and list 2 (serial numbers actually, some of which have letters in the serial number, so text as far as excel is concerned).
What formula can I use to show in a cell next to a serial number in list 1, that it isn't not on list 2.
Let me write that again...
It should be simple, but when I ask on other Excel forums I get overly complicated answers, that don’t work.
Basically, I have TWO lists of numbers, list 1 and list 2 (serial numbers actually, some of which have letters in the serial number, so text as far as excel is concerned).
What formula can I use to show in a cell next to a serial number in list 1, that it isn’t not on list 2.
Posted 18 minutes ago
... And each the lists wont be in and ascending/descending order - listed by location of items.
A variation on this should do it - using a vlookup but within an IF as per second version. The nil return column could be altered to "no match".
If you have the first list in column A and the second in column B, in column C you could put something like:
=IF(COUNTIF(A$1:A$8,B1)>0,"Yes","No")
and fill down.
That'll give you a "Yes" if the adjacent cell in column B appears in the range (the bit in bold, change it so it matches the length of your list) given in the formula for column A
Edit, that might be back to front, if it is swap the columns for your two lists.
Rightio, I think I understand... I'll give it a go.
vlookups are old now. Xlookups are the future.
=xlookup(lkup value in list one, range to lookup against in list two, range to return in list two (in this case probably the same as list one), value to return if not found (could be zero or could be "not found"),0,1)
or [list 1 in A1:A10, list2 in B1:B10]
=IF(ISNA(MATCH(A1,$B$1:$B$10,0)),"No","Yes")
and VLOOKUP ha always been rubbish, so don't even go there
XLookup is worth learning even if countif works in this case.