So you use something like:
=vlookup(a2,othersheet!a:a,1,0)
If you see an n/a error, you know that there isn't a match???

It might make more sense to use =match() to look for a match <vbg.

=match(a2,othersheet!a:a,0)
This will return a number (the row number) where the first match occurs -- or
that same N/A error.

=isnumber(match(a2,othersheet!a:a,0))
will return a true/false (for match or no match).

If you wanted to look for a match between two columns:

=match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

You could wrap it inside =isnumber(), too:
=isnumber(match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))

=========
You didn't ask, but here's a post I've saved for when I want to return the first
match based on multiple columns:

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))



rpm1983 wrote:

I have 2 spreadsheets that both contain store numbers and the accounts they
belong to. I want to compare the two files to make sure that all of the store
numbers in one (smaller) list are included in the other (master) list.

I usually use the VLOOKUP formula to search for the store numbers in the
small file and return the same values in the master file. In this instance,
some of my store numbers are duplicated across accounts. For example, ABC
retailer and XYZ retailer might both have a store #123.

I want to do the same thing I have been doing with the previous VLOOKUPS,
but restrict my queries to the specific account. In other words, I want to
VLOOKUP store #123 but only where account = ABC.

--

Dave Peterson