Vlookup with Multiple Criteria

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. 
0
rpm1983 (2)
6/10/2008 10:46:00 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
354 Views

Similar Articles

[PageSpeed] 59

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
0
petersod (12005)
6/11/2008 12:37:37 AM
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.


 
"Dave Peterson" wrote:

> 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
> 
0
rpm1983 (2)
6/11/2008 2:40:03 PM
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???)

rpm1983 wrote:
> 
> 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.
> 
> 
> "Dave Peterson" wrote:
> 
> > 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
> >

-- 

Dave Peterson
0
petersod (12005)
6/11/2008 4:51:03 PM
Reply:

Similar Artilces:

OWA FE/BE and multiple child domains in a forest
Hello all i am hoping someone can help me with this issue. We currently have OWA working in a FE /BE configuration. Is it possible to have the same front end server and a differetn B/E server in a different domain within a forest work? We have 2 domains that have a 2 way trust between them ABC and XYZ. LIkewise our email addresses are abc.com and xyz.com. We can get to abc.com email from OWA no prolbem. We tried logging into the xyz domain and it seems to authenticate but when the mailbox comes up it says that its loading and stays that way. The logs show they authenticated properly but...

Complex Query question
I've got a challenging query I need help with! Here's a snapshot of the tables and relationships. I've put the relevant tables into this query layout just for illustrative purposes. ----- click on it to make it bigger (it's still a little hard to read) http://tinypic.com/view.php?pic=6xu9oh4&s=1 Note - the table names start with phrases that are not relevant to this question (ie I use the word "record" for another use than the normal database usage of that word). ------ I'll explain the setup. I want the Table Record-Orders-Sales to lookup Record-Cur...

Multiple Inboxes #2
I have multiple E-Mail accounts and want to separate the mail. How do I designate a separate inbox for each ? "Bud Davis" <anonymous@discussions.microsoft.com> wrote in message news:0dbe01c3df52$d8ef9c80$a001280a@phx.gbl... > I have multiple E-Mail accounts and want to separate the > mail. How do I designate a separate inbox for each ? Rules wizard. ...

Multiple Data Migrations
We have our initial information in CRM. Can we do more data migrations of new accounts and products with our current CRM data intact so that it will just append to what we have out there? Yes, according to the documentation, the DMF always appends. Regards, Matt Narayan. >-----Original Message----- >We have our initial information in CRM. Can we do more >data migrations of new accounts and products with our >current CRM data intact so that it will just append to >what we have out there? >. > Yes and No - if you already have the Accounts and Products in CRM you...

VLOOKUP #5
Hi, Can someone please help me. I am using Vlookup to look up a table and bring nack relevant information. When the information is not found vlookup returns N/A. Is there any way of vlookup returning an empty cell if no info is found Rgds Gordon =IF(ISNA(VLOOKUP(...)),"",VLOOKUP(....)) HTH -- AP "gordo" <gordon.brown24@btinternet.com> a �crit dans le message de news:1143878499.265471.15390@z34g2000cwc.googlegroups.com... > Hi, > Can someone please help me. I am using Vlookup to look up a table and > bring nack relevant information. When the informa...

Pivot Tables & Multiple Row Entries
Hi, When there are 1 or more descriptive fields in a pivot table and the entries in field 2 or further on are repeated, the values in the previous field are left blank for each repition of the subsequent field. This causes problems for further sorting etc...unless you use formulas. Does anybody know how to ensure that all cells in the descriptive fields (rows in the layout section) are populated with the appropriate value? Thanks POD -- POD ------------------------------------------------------------------------ POD's Profile: http://www.excelforum.com/member.php?action=getinfo&...

Why does Publisher not allow multiple page email newsletters?
Just received Publisher 2003. I'm trying to make an email newsletter and trying to incorporate the "more details" links embedded into the template. However, if I want that to be a link to another page in the document, it doesn't work if I send the .pub as a message in an email. It doesn't work any way I save it, .mht, .tif, etc. I'm curious why I had to spend two days sorting out the whole "more details" links through the help files only to find that they're useless unless I have it link to some other arbitrary site. I bought publisher for thi...

How to personalize emails to multiple recipients?
Hello, I'd like to "personalize" emails I send to many recipients. That means easy things like having the recipients in some format db or Excel and then send it too all of them with having everyone addressed like "Dear John" etc. Is there any option I could do that with, like using fields or something? Thanks for your help Thomas Use Mail Merge. Help has directions. "Thomas" wrote: > Hello, > > I'd like to "personalize" emails I send to many recipients. That means easy > things like having the recipients in some format db or...

How to look up a value in a list and return multiple corresponding
I followed the instructions on the excel help page of how look up a value in a list and return multiple corresponding values but somethings not right. I have tried this numerous times in my excel spreadsheet and it does not return any values let alone numerous values. No values will appear unless I go into the insert menu and click function, but then here it will only give me the smallest value, even when there is more than one. Please help asap! Thank you. How about giving us some details? -- Biff Microsoft Excel MVP "123456789" <123456789@discussions.m...

VLookup Returning N/A
Hello: I having a problem with a VLookup formula. My main spreadsheet is a download from a mainframe and the lookup table (on a separate spreadsheet) is inputted directly into Excel. The lookup value is a PO number. All the VLookup Formulas are returning N/A. The weird thing is that if I manually type in the PO Number in the main spreadsheet, then the formulas work. Or if I copy the PO Number from the main spreadsheet into the lookup table, then the formulas work. Obviously the PO numbers in the main spreadsheet and the PO numbers in the lookup table aren't matching, but I can't f...

Multiple date validation in a single cell
I am trying to do a rather complicated validation (I think it i anyway!) I want to allow ONLY dates of a particular format to be entered int the one cell - i.e. YYYY OR mm/YYYY OR dd/mm/YYYY For example, the same cell can have either one of these format entered, but MUST match one of them to be accepted. 1970 is OK and is captured in the cell as 1970 (not 1/01/1970) 70 is assumed to mean 1970 on entry 05/1970 is OK, captured in the cell as 05/1970 (not 1/05/1970) 5/70 or May-70 is assumed to mean 05/0970 on entry 05/06/2004 is OK 5/6/04 or 5 June 04 is assumed to mean 05/06/2004 on entr...

How do I add .htm to every link in a worksheet? Multiple hyperlinks+targets
I have about 500 hyperlinks in my worksheets. An example of 1 of th links = "..dvd/1994/940602" As you can see, i forgot to use the file extension i wanted to link t (*.htm) Is there a way to edit 500 links by automatically adding .ht to each one with out me going the manual route. i "..dvd/1994/940602.htm" Also how do i change the "_parent" to "main" so all the links open i the main window, and not just a new page. When i have the page saved as html, i can go in find and replace i wordpad which easily changes the links (searched on "target =...

Multiple IF Statements
Hello, I'm trying to write an IF statement that has 3 possible outcomes. I have written each statement on their own and they all work, but I can't link them together. What I would like to is: IF cell E42 = "3 Months" =IF(E42="3 Month", VLOOKUP(A45,Data!$A$2:$E$13,3,FALSE)) Else if cell E42 = "12 Months" =IF(E42="12 Month", VLOOKUP(A45,Data!$A$2:$E$13,4,FALSE)) Else if cell E42 = "24 Months" =IF(E42="24 Month", VLOOKUP(A45,Data!$A$2:$E$13,5,FALSE)) Any help would be great! Thanks Maybe... =vlookup(a45,data!$a$2:$e$13...

Multiple Personal Folders in Folder List and Mail Navigation panes
Would greatly appreciate help. I have two Personal Folders (both with the same sub folders: Inbox, Sent Items, etc.) in the Folder List and Mail views in the Navigation Pane. With both, when I right click on them, the selection "close personal folder" is grayed. How do I get rid of one of them?? Thank you. If they are true personal folders then you must go through tools, services, highlight appropriate personal folder and remove it there. This is for Outlook 2k, you didnt specify which version. >-----Original Message----- >Would greatly appreciate help. >I have two ...

how would I write this criteria for a query?
I have three fields that I want to base a criteria on: Received Date/Time, Date Approved and Date Rejected The way I wrote the criteria is as follow: LagTime: [Received Date/Time] - [Date Approve] This works if there is a date in the "Date Approve" field but if there isn't a date in the field nothing comes back. What I would like for it to do is pull the date from the "Date Rejected" field and calculate the lagtime from there. What do I need to add to my expression to do this? On 6 Sep, 20:14, Jerry Whittle <JerryWhit...@discussions.microsoft.com> wrote:...

Expressions using multiple records
I am tracking production equipment effectiveness. Operators are selecting the tool designator from a list, entering an event type and the date/time of the event start. A query pulls out records for a particular tool, sorted by date/time. I'd like to add a calculated field that shows event duration, but that involves using the event start date/time from more than one record. In other words the event duration is defined by a record's start date/time and is cut off by the following record's event start date/time. A simple subtraction if I can pull in the same field from tw...

Documents in Multiple Monitors
Excel and Powerpoint (even in Office 2007) make it ludicrously hard (impossible?) to view two documents on multiple monitors. Even seeing them side by side is unintuitive (compared to Word). Will this be finally addressed in Office 2007? It wasn't as of the last beta I tried. -- -C. Moya www.cmoya.com I'm able to do it with my multiple monitor setup. What problems are you having? How are you trying to do it? Craig CMM wrote: > Excel and Powerpoint (even in Office 2007) make it ludicrously hard > (impossible?) to view two documents on multiple monitors. Even seeing ...

Easiest way to insert multiple copied rows?
Version: 2004 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Currently, I copy a row, right click and choose insert copied cells. <br> This works great but only works for copying one row below. <br><br>What if I want to copy 2 or 3 rows below? <br><br>I can find blank rows, copy them, and insert them and then do this, but that is real tedious. <br><br>I wish there was a was to simply copy a row and hit some keystroke which would copy that row right below as many time as you click the keystroke. Does this exist? thanks! Try clic...

In Excel 2007 chart with multiple lines, mouse doesn't track lines
If I open an Excel 2003 x-y scatter line chart with multiple (x,y) line graphs (or open a graph that was saved under Excel 2007), and I move the mouse over the various lines (some of them may be close to each other) then there is an x 'offset' in the location of the mouse and when Excel recognizes a line graph and displayes the line title with a little message underneath the mouse cursor. Another way to explain thsi behaviour is if you try to click at a particular line graph, it may not select it, it may select a different line graph that is located left to where the mouse is. ...

Multiple Packing slip numbers for Order Fulfillments
For split shipments of a line item on either an order or invoice, it would be nice to be able to have multiple packing slip numbers associated with the line item. This would be similar to the POP handling of receipts. You can have multiple receipts with multiple receipt numbers on one line item of a PO. Currently in SOP if you have multple shipments on one order they have the same packing slip number with a "reprint" logo on top of the packing slip. On the other side, when the customer receives this and puts it into their POP system they are now going to have two documents ...

saving excel with multiple sheets, but won't save to proper sheet
I have a shared worksheet in Excel that several people save to throughout the day. It has several sheets in it and just in the past couple of days, when people save, they either lose their changes, or the information is saved to a blank sheet that has not been used yet. Has anyone else had this problem and if so, what is the solution. ...

Outside Recipents Recieving Multiple Copies of an Email
Exchange 2003 Server I am currently trying to find clues to a strange email problem. Recipents outside of my domain are recieving multie copies of single emails sent to them from users inside my domain. There is no pattern, sometimes it is 10 copies sometimes it is 113 copies. The Queue for the recipent domain in Exchange has the Status of " The connection was dropped by the remote host. " Any advise, hints, ideas are welcome. Thanks John Newton Look at your SMTP logs to see if there is any more information. Do you have Exchange aware AV on the server? Have you looked into ...

Multiple ListBoxes, OptionButtons, Check Boxes Etc.
I would like to place multiple ListBoxex, Option Buttons, or Check Boxes etc. on a sheet. When I do that, they seem to all interact with each other and I can't figure out how to make them act independently. Two check boxes are Ok since one can be "yes" and the other "no" for instance. I would like to have a large list of items and the user can select only the ones they want to use, which in turn will create a "sub list" if you will. I'm open for ideas on doing it some other way than list boxes, check boxes or whatever also. Any ideas? Thanks in a...

multiple series from one column
Hi, my data are organized in three columns: X, Y and a column with the series names (say A, B and C). Is it possible to create a single XY plot for the three series without selecting the series separately? Can you make Excel recognizing the three series in the third column? It would be very easy if you could simply select the three columns and define them as X, Y and series. Especially if you have a lot of series... You're asking more of Excel than it was designed for. However, it is designed for data processing. Make a pivot table of your data, which will allow you to separate th...

multiple indexes in one document
I have a document that lists information in a table. This information has to be split into two indexes in separate parts of the same document. Trick is that one index will need to contain all of the information and the other just certain pieces. I created the main index and it is fine, however I do not know how to double mark entries for use in the second index. There are two separate mechanisms for this, and you can use one or the other. (I would discourage trying to use both at the same time.) If all the items for the second index are together in one part of the table...