Two Condition Vlookup

I have two worksheets that contain similar information. What I need is a 
formula that will lookup the Value of column A in worksheet one, but also 
there’s a second condition that I want it to meet which is in column B. So 
basically if these two conditions are met then I want to see the result from 
using worksheet 2 as the lookup reference table. Does anyone know the correct 
format for a  two condition Vlookup? Any help is greatly appreciated.
0
Utf
3/7/2010 11:34:01 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
1381 Views

Similar Articles

[PageSpeed] 40

You have a few choices...

One is to insert a new column A in the rates worksheet.

Then you could concatenate the values in the new column B and C into column A.

=b2&"|"&c2
(and drag down)

Then you could modify the =vlookup() to look at this new column--but concatenate
the year and name into the value to match:

=vlookup(a2&"|"&b2,othersheet!a:e,3,false)
where a2 holds the year and b2 holds the name.

There are other ways, too.

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))

============
If there is only one match and you're bringing back a number (or 0 if there is
no match for all the criteria), you can use:

=sumproduct(--(othersheet!a1:a10=a1),
            --(othersheet!b1:b10=b1),
              (othersheet!c1:c10))

Santi wrote:
> 
> I have two worksheets that contain similar information. What I need is a
> formula that will lookup the Value of column A in worksheet one, but also
> there’s a second condition that I want it to meet which is in column B. So
> basically if these two conditions are met then I want to see the result from
> using worksheet 2 as the lookup reference table. Does anyone know the correct
> format for a  two condition Vlookup? Any help is greatly appreciated.

-- 

Dave Peterson
0
Dave
3/8/2010 12:01:41 AM
Reply:

Similar Artilces:

Vlookup with variable column reference
Help! I am trying to link two workbooks with a vlookup that searches for the last populated columns' entry in a range (see example below). 27/03 28/03 29/03 30/03 31/03 Data 6 4 Is there a way of doing this in a function? In a macro I would use the Range().End(xltoleft) code but I cannot figure out how to do something similar in a worksheet function. I basically want the lookup column to start at day 31 and make its way backwards until it finds a value. Another issue is that the seemingly blank cells contain formula - ie. not empty. Any ideas? Thanks!! Jen 1 ...

conditional summing of arrays
I would like to sumproduct 2 arrays up to the row where it first exceeds a given number. For example assume 2 arrays are as follows : 1,2,3,4,5 & 2,3,4,5,6 the sumproduct is 2,8,20,40,70. So if my given number was say 30, the answer would be 4. if the number was say 80, the result would be NA. Can anyone help me with this problem? Thank you and kind regards JV ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Software! http://www.ozgrid.com/Services/excel-software-categories.htm ** This can probably be done easier =INDEX(LARGE(ROW(INDIRECT("1:"&...

Conditional format to cells containing a #DIV/0! error
I would like to apply a conditional format to an entire spreadsheet. In particular, if a cell returns and error such as #DIV/0! I would the selected font color to be white. What do I have to enter in the conditional formatting menu? If I set the value of the cell equal to #DIV/0!, the format does not work. A solution that I found could be to use the GoTo functions to select the cells with errors and then manually apply the color, but since I am creating a template at a later point the empty cells that return the divided by zero error may contain a number. Basically I would like the cells t...

vlookup from two sources
[Excel 2003] Is it possible to use syntax to perform a vlookup from a source and then another if the first is False? For example, My primary table of data is called "Materials" and my second source is a range A81:E140 on the same sheet as the vlookup, So something like : =vlookup(A1,Materials,2,vlookup(A1,A81:E140),2,False) Can anyone help with advice on syntax? Thankyou, Roger the false in a Vlookup is the mtach type, not an value to use if the formula is false. =if(iserror(vlookup(A1,Materials,2,false)),Vlookup(A1,A81:E140,2,false),Vlookup(A1,Mate...

Can't open two Documents with same name !!
Hi: I install Office 97 in WinXP, works good except when I open a excel spreadsheet a windows always opens saying Can't open two Documents with same name. I click ok and everything works OK. How do I stop this warning I get all the time when I first open a Spreadsheet. Thanks Debra Dalgleish has a list of frequently asked questions at: http://www.contextures.com/xlfaqIndex.html This one sounds like yours: http://www.contextures.com/xlfaqApp.html#AlreadyOpen Owen wrote: > > Hi: I install Office 97 in WinXP, works good except when I > open a excel spreadsheet a windows alway...

keeping two computers updated with one money file
I use a desktop at home and a laptop when I am on the road. What I would like to do is be able to keep my money file syncronized between my two computers. Money offers nothing that will help with this. There is no good way to do this short of something like WinXP Synchronization Manager. I can't say as I've tried it or read here of someone who has. Is that an answer to the question you never asked? "JB" <JB@discussions.microsoft.com> wrote in message news:95B9AF2E-EBD5-4D09-826A-1430BF6C035E@microsoft.com... >I use a desktop at home and a laptop when I am on ...

Finding the difference in days between two dates?
Hi, I was wondering if anyone could let me know how I can find the difference in days between two date fields, would appreciate the help. Thanks Damon Damon; Just try this. Enter two dates ( 01/08/2003 and 21/08/2003 ) In another cell subtract the two dates, but format that cell as "General". If all is well, just must find a 20 in that cell. Or is it too simple and not what you want ? Mark Rosenkrantz. More Excel ? www.rosenkrantz.nl or info@rosenkrantz.nl ----------------------------------------------------------------- "Damon" <damon@nospam.co.uk...

Conditional Delete
I want to delete all cells that contain specific text. Any help would be appreciated! I'd select that column and do Data|Filter|autofilter Then use the dropdown and choose custom contains: (type in your value) And delete the visible rows Then data|Filter|autofilter (to remove the filter) ======= You may be able to chose the value you want from the dropdown, too--avoiding the Custom stuff. gsxith3@embarqmail.com wrote: > > I want to delete all cells that contain specific text. Any help would > be appreciated! -- Dave Peterson Edit>Find what: yourtext Find all. ...

How to update a portion of rows and columns between two files
I have a Master file with 20 columns of data for each of approximately 700 rows. Each month, I receive an update file that I need to use to update 10 columns of data for 3/4 of the rows in my Master file. There is a unique identifier in each file. Not all rows in the update file will be copied into the Master file and not all rows in the Master file will have updates. What is the best way to do this? I am familiar with VLookup. Shall I write aVLookup for each of the 10 columns in each row that need to be replaced so that I can grab the cell from the new file? Or, is there an easier way? ...

Creating a Report using two queries
I have created a chart report (bar chart) using one query. How can I create another single chart showing the results of both queries. One will show the bars of first query data and the other will show bars of the data from second query together in a single chart report. Any suggestions? -- FL This depends on how/if the two queries are related. I would expect the "X" axis values might be related. If so, join the two queries into a single query and graph the new query. -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to UCP http://www...

Conditional Formatting in data table of a chart
I'm using Excel 2007, and trying to apply conditional formatting to the data table of a line chart. I've tried creating the chart without the formatting applied to cells with the original data (data source), and then applying the format to the source; as well as creating the chart after applying the format to the source. The data table in the chart will naturally update with changed data since it is linked, but will not incorporate any style formatting. Any thought? Thanks. Data tables in charts are not particularly flexible. They do not, for example, support conditional fo...

JDBC-ODBC Bridge driver & ODBC driver are two different installati
http://www.paphoscarhire.mobi/odbc-bridge2.jpg desktop control panel settings, I have above URL (IMG) Settings well, I need to run in my PC: Type 1 db driver Access / java[SE1.6] Desktop Applications, what I need to install ? I refer for Type 1, JDBC-ODBC Bridge driver.... Is it needed install and Access 2003 ODBC driver (see URL) ...? How I understand if I already installed it(?) since I do not know ? JDBC-ODBC Bridge driver & ODBC driver are two different installations? How to load driver in my program ? The below are correct for: Class.forName & DriverManager.ge...

Comparing data sets with +/-1 tolerance using conditional formatin
Please help! This has been puzzling me for hours!! I am using conditional formating to compare one data set with another to see if there are any discrepancies, using green cells for the same and red cells for different data. However, one set of data is rounded to the whole number, but the other set is to 2 decimal places (and has to remain that way). Therefore I need my conditional formating to compare the two data sets, but with a tolerance of plus or minus 1.00. How do I create this formula using conditional formating? CF/ Formula is: =ABS(A1-B1)<=1 -- David Biddulph ...

Two POP accounts with separate data files?
I'm using Outlook 2003. I've got two POP accounts, but I don't want mail for both to be delivered to the same Data file (Personal Folder). Also, I'd rather not use a rule to have mail from the second account simply delivered to a different folder. Is there a way to have two separate sets of "Personal Folders" and have one account deliver to one and the second account deliver to a second? Thanks. Your name <brown_sm@nspam.yahoo.com> wrote: > I'm using Outlook 2003. I've got two POP accounts, but I don't want > mail for both to be del...

Range Vlookup
Hi Every One, I hope you all are doing well, I required your help to resolve my issue in excel. On single worksheet i have two different sheet name as 1.IP Range 2. Result. Question: EG:IP Range Start IP End IP Area 192.168.1.4 195.182.254.254 AG 10.15.33.10 10.18.56.254 EMEA 10.128.33.5 10.132.40.60 AP Here it will Continued as well. On Result sheet i required result like: IP Area 192.170.30.30 AG 194.168.10.20 AG 10.131.37.20 EMEA 10.170.255.255 NA 10.129.36.8 ...

how do you compare two dates to find the difference?
mm/dd/yyyy is the format. I need to subtract two dates to find the difference Hi =A2-A1 and format the result as 'Number' -- Regards Frank Kabel Frankfurt, Germany Todd wrote: > mm/dd/yyyy is the format. I need to subtract two dates to find the > difference With dates in A1 & A2, in A3: =A1-A2 This will give you the number of days difference. Format A3 as general "Todd" <Todd@discussions.microsoft.com> wrote in message news:19B34084-E5BF-4D21-BB4E-DE60DB66CAE3@microsoft.com... > mm/dd/yyyy is the format. I need to subtract two dates to find the ...

[B]Conditional Macro?[/B]
Hey. Is it possible to activate a Macro when a cell contains a certai value? or anything simila -- Heydilber ----------------------------------------------------------------------- Heydilbert's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2874 View this thread: http://www.excelforum.com/showthread.php?threadid=48441 Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "H1" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target ...

"A configuration error.. caused the message to bounce between two servers"
For a few weeks now I've been trying to debug the connection between two exchange 2k servers on my network. A new one and an old one. Users on the old server couldn't send messages to users on the new one and vice versa. I seem to have partially fixed the problem by Setting our two internal MX records to have the same metric or weight or whatever you want to call it. Now the origional server can send messages to the new server. The new server however still cant send messages to the old server. Anything you try to send in that direction instantly gets the following message...

Conditional formatting with linked value
I'm Paste Special/Linking formatted dates (dddd, mmmm dd, yyyy) and text values in a calendar type list - linking to a copy of the calendar. When I don't have any dates or values on specific lines, I want the copy calendar to have blank lines. However, because I format the date I get a '0' in the 'blank' line. Or if I don't have a date on the line I get 'Sunday, January 0, 2000' in the copy calendar line. I've been trying to use the conditional formatting (I use Excel 2007), using white as the color for the lines I don't want to show, but can...

Conditional format on data change
I've a 5-column table with financial data sorted by the field "payee". As the table is extensive I would like to highlight the row(preferably) or the cell at each data change in the payee field. Can this be done? I put some text in A10:A20 (a,a,a,b,c,c,c,........) Selected A10:M20 Used Format | Conditional Formatting with -> Formula is $A10<> $A9 opened the Pattern tab and selected a fill colour Now the rows in range where text in A changes are coloured green Note it colours the first row (we could do something about this if important) best wishes -- Bernar...

Can you conditional format bar and pie charts in Excel 2000?
Hello, I don't know if this is possible in excel 2000 but I figured I woul ask a quick question instead of continuing to spin my wheels on it. I there anyway to automatically conditional format the color of a pie o bar chart. In other words, I have a table of data that is going t change from month to month. The table I am graphing looks like this: Project Size Status Project A $2m Green Project B $.5m Green Project C $1.2m Red The number of projects and status' could change from month to month. want to create a pie (or bar) charts that will auto format the...

Combine Two Colums
Hello All Need help with the script below or maybe a better one. I need to combine the items in Column A and Column B into one Column The one below will combine the data into one column but it is backward, I need the text in column B to be added after the text in column A Thanks for any help Scott Sub addData() For r = 2 To 100 Cells(r, "D").Value = "@website.com" & Cells(r, "D").Value Next r End Sub This is what I have A B C D E 1 Jimmy @website.com 2 Jimmy @website.c...

Conditional Formatting 11-30-09
New to this.... I have a list of dollar amounts in column A. In column B, a 1 or 2 (yes or no) depending on whether the bid was awarded or not. I need to create a running total everytime a 1 is added to column B that equals the total of the dollar amounts next to each "1". =SUMIF(B:B,1,A:A) will work provided the columns have nothing else in them but the data for this topic. Otherwise, somethink like this with your actual ranges =SUMIF(B1:B100,1,A1:A100) BTW, this is not 'formatting' - that only changes the appearance of things. You need a formula to cond...

How To Do Vlookup But Have The #n/a Be 0
hi, does anyone know how to make vlookup instead of #n/a it appears a (zero) thanks :confused -- Message posted from http://www.ExcelForum.com sokevin =IF(ISNA(VLOOKUP(value,table,col,FALSE)),0,VLOOKUP(value,table,col,FALSE)) Gord Dibben Excel MVP On Wed, 18 Feb 2004 23:48:07 -0600, sokevin <<sokevin.11uzu5@excelforum-nospam.com>> wrote: >hi, does anyone know how to make vlookup instead of #n/a it appears a 0 >(zero) > > >thanks :confused: > > >--- >Message posted from http://www.ExcelForum.com/ thank moit : -- Message posted from http://www...

How many quarters between two dates?
Hi Everyone! Got a date question for you. I was wondering if anyone knew of formula where I could do the following: Withing a column, find the oldest date, and the most recent dat listed. Then, find out how many quarters (three months) fell betwee those dates. Anyone know?? Any help is of course greatly appreciated. Thanks! -- Message posted from http://www.ExcelForum.com try: =DATEDIF(MIN(A3:A8),MAX(A3:A8),"m")/3 "Rich9016 >" <<Rich9016.14vysl@excelforum-nospam.com> wrote in message news:Rich9016.14vysl@excelforum-nospam.com... > Hi Everyone! &...