Vlookup with Multiple Criteria

  • Thread starter Thread starter rpm1983
  • Start date Start date
R

rpm1983

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.
 
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))
 
Thanks Dave,

Almost there, but there are two criteria in your index/match that I don't
understand (when I tried to apply this in my spreadsheet I got all N/A's).

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

What do the 1 and the 0 refer to in the match function? When I begin typing
the match formula, Excel says the first criteria it is looking for is the
lookup value. This is where you have a 1. Shouldn't this be the value that I
am trying to match in the other sheet?
Also, should the formula be returning a true/false, or will it return the
matching value from column C in the lookup sheet?

Thanks for the help.
 
This kind of expression:
(a2=othersheet!$a$1:$a$100)
will return an array (100 elements) of true/falses--depending on if A2 is equal
to A1, A2, ..., A100 on the othersheet.

When you do this:
(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100)

You get an array (still 100 elements) of 1's and 0's because:
True * True = 1
False * True = 0
true * false = 0
false * false = 0

So in that =match(), we're actually looking for the first 1 in that array:
=match(1,{0,0,0,1, 0, 1, 0, ..., 0}, 0)
(the last ", 0)" means exact match)

If you use the =isnumber(match()) version, you'll see true/false.

If you use the =index(match()) version, you'll see the value from column C for
the first match where both column A and B match A2 and B2.

If there is no row with both matches, then you'll get the #n/a error returned.

And don't forget to use ctrl-shift-enter to array enter your formula.

(Ps. You did remember to change the worksheet names and the addresses of those
ranges, right???)
 
Back
Top