Comparing a cell value against a range of values

[AliHurworth]

Hi all,
I have a spreadsheet which has a column of data (actually, shopping SKU's). Another column (shorter) has a another list of SKU's, some of which are the same as in the first column. I'm using Excel's MATCH function to, well, match them in a true of false. But as the range extends to more than 3000 lines, I'm struggling to increment the formula eg: turn

=OR(EXACT(A1, B1:B1000))

into

=OR(EXACT(A3000, B1:B1000))

.

It's probably quite straightforward, but can anyone show me...?

Thanks

[Jonmo1]

You probably just need to lock the range reference to B1:B1000

Try
=OR(EXACT(A1, B$1:B$1000))


Also, Match will be MUCH more efficient than that.
Unless Capitalization is important (jon should NOT be considered a match for JON)

=ISNUMBER(MATCH(A1,B$1:B$1000,0))

[ARGK]

Hi Ali
Put this formula in the first row and copy down:

=OR(EXACT(A1, $B$1:$B$1000))

Good luck.
Tony

[AliHurworth]

That works great thanks!!

[Jonmo1]

You're welcome.

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.