Excel help.. sorry

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.

I have a formula that does this. ‘Does [this] appear in [this range]’. if I remember correctly it is similar, if not identical, to @mrjmt ‘s formula.

that it isn’t not on list 2

<syntax error>

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)

The countif answer is the easy one. Lookups are for returning a value, you just need to see if it's in the list.

@kelron

Rightio, I know what to do with COUNTIF functions so I'll give that a bash.

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.