lookup second / last match with two conditions

Hello,

I've a table below, I want to setup an formula to get the result as shown at 
the bottom.  Thanks in advance for your help.

Column	B	C	D
Row   1st criteria  2nd criteria  lookup value
3	A	X	1
4	B	Y	2
5	C	Y	3
6	A	Y	4
7	B	X	5
8	C	Y	6
9	D	X	7
10	A	Y	8
11	B	Y	9
12	C	X	10
			
                      query 1   query 2
criteria 1	          A	        C
criteria 2 	          Y	        Y
nth of match        2	       last
Result	          8	         9 
			

0
Utf
3/18/2010 9:39:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
1140 Views

Similar Articles

[PageSpeed] 6

Please clarify:

Why do you want 9 for query 2 rather than 6??
-- 
Gary''s Student - gsnu201001


"Billy Leung" wrote:

> Hello,
> 
> I've a table below, I want to setup an formula to get the result as shown at 
> the bottom.  Thanks in advance for your help.
> 
> Column	B	C	D
> Row   1st criteria  2nd criteria  lookup value
> 3	A	X	1
> 4	B	Y	2
> 5	C	Y	3
> 6	A	Y	4
> 7	B	X	5
> 8	C	Y	6
> 9	D	X	7
> 10	A	Y	8
> 11	B	Y	9
> 12	C	X	10
> 			
>                       query 1   query 2
> criteria 1	          A	        C
> criteria 2 	          Y	        Y
> nth of match        2	       last
> Result	          8	         9 
> 			
> 
0
Utf
3/18/2010 12:38:01 PM
Reply:

Similar Artilces:

Shared calendar between two profiles?
Is there a method or an external application that enables me to have two separate profiles that share the same calendar? -pnp Connect to the pst-file of the other profile by File-> Open-> Outlook Data File... -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "pnp" <pnp.@.softlab.ntua.gr> wrote in message news:%23IaTAIu%23EHA.1392@tk2msftngp13.phx.gbl... > Is there a method or an external application that enables me to have two > separa...

Field name into two lines in a query?
This may be a silly question but... Is it possible to make the name of a field into two lines instead of one long line in a query? No, there is no way to do that. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "mayj" <mayj@discussions.microsoft.com> wrote in message news:9372FB14-BDDE-49E3-8A0E-7E5F3B598FAC@microsoft.com... > This may be a silly question but... > > Is it possible to make the name of a field into two lines instead of one > long line in a query? O...

Quick way to change display name from first last to last, first
Is there a quick way to change the display name from first last to last, first other than manually or manually editing a .csv export file? Many thanks, Brian That is the only (Microsoft) way in Exchange 5.5 You can modify settings in the admin to do this for new users. -- Hope that helps, Dan Townsend This posting is provided "AS IS" with no warranties, and confers no rights. Please do not send email to this address, post a reply to this newsgroup. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm "Brian Bri...

Conditional formatting query #2
Hi, I thought I knew how to do this, but it turns out I don't. If I have names in column A (eg Alan, Bob, Cathy), and values in column B (see below), is there any way I can use conditional formatting to change a cell in A, based on a value in B? A B 1 Alan 4 2 Bob 1 3 Cathy 3 Essentially what I'd like is to highlight the name, if the value is 3 or over. I know how to change the cells in B using conditional formatting, but can't figure out how to change A cells. Thanks, Fiona Select all the cells you want Conditionally formatted. Try this with Formul...

remove last three characters of cell
Thanks, Access Expert! Your formula works really great! Now, I am trying to use the same formula to remove the last three characters which contain a comma, a space, and one letter (it varies, and a wildcard doesn't seem to work) (e.g. ", J"), how do I modify it to remove the last three characters. I've been playing around with it, but don't know how to specify the letter as a wildcard, if that's even possible. I want to to go from Doe, John, W --> Doe, John Thanks! "AccessExpert" wrote: > Mira, > > You can use a combination of the L...

More conditional formatting
I would like to change the pattern colour of a cell based on what pattern colour another cell is, e.g. if cell b3 is red with no text in, I would like cell a1 to be red also. Does anyone know how to do this? -- Jon Seddon Hi not possible without using VBA (to get the color of the other cell) It wuld be easier if you could use a condition which is not based on a format >-----Original Message----- >I would like to change the pattern colour of a cell based on what pattern colour another cell is, e.g. if cell b3 is red with no text in, I would like cell a1 to be red also. Does anyone...

How to add Custom Lookups
Hello all, I am working on an custom integration to CRM. I desperately need the option of allowing the users to select a value from a custom lookup. unfortunately, the pickuplist option only allows the items to be manually maniplated using the forms customization. I need an option to feed in the data thru api or some other means. comments!! If we manipulate the xsl files and add any customizations drectly then it will be overwritten the next time we publish any changes. By customization I mean, for ex:adding a button on to the form. Any other suggestions. regards rojer You de...

Lookup value off by one row
Hi, Any help you can provide on this would be appreciated. I have a worksheet that identifies how far a number is from target. I created the lookup below to coorespond to the productivity increase for next year. =LOOKUP(C4,LookUpValues!$A$1:$A$402,LookUpValues!$B$1:$B$402) C4 is -10.9, in the lookupValues tab -10.9 is in row 93 column A as is the desired result return of 5.6 in column b, however the lookup function is returning 5.7. The issue may be that the data and the lookupValues tab numbers are not exactly the same. -10.90001 and -10.9004 may display the same, but w...

Lookup on custom entity without relationship
First,I will show existing out-of-the-box sample in MS CRM3: Address entity is in relation to account M:1.When I stay on order entity and press on lookup addresses I get result filtered only for account selected in order. Order there is no relation to address entity. My problem: I created new CustomEntity1 with relationship to Account 1:M. Now, I want to add to Order field from CustomEntity1, filtered only by currently selected in order Account.If I add relationship CustomEntity1->Order, I think, I will get in lookup all CustomEntity1 records without filter by order account. Please help ASA...

Index & Match on Pivot Table
Hi, I'm using Office 97 - and was wondering if there is a way to do an Index (Match), (Match) type function on a Pivot Table? I've tried it - but I get a #value error. Does anyone know a way around this? Nelson Nelson wrote: > Hi, > > I'm using Office 97 - and was wondering if there is a way to do an Index > (Match), (Match) type function on a Pivot Table? > > I've tried it - but I get a #value error. Does anyone know a way around > this? > > Nelson > > -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextur...

DNS issues, reverse lookup, MX records
Hello, I've done some searching in the groups, but I'd like to pose a specific scenario. We are getting these errors: #4.7.1 smtp;450 4.7.1 Client host rejected: cannot find your reverse hostname, This is the situation. Our domain name is registered with network solutions. Our website is hosted with Yahoo on a business account. Yahoo used to host our email as well. Recently we put in a local SBS 2003 server to host our Exchange locally. Originally our Network solutions account had all DNS pointing to Yahoo's name servers, as it should have. We wanted to switch the MX reco...

WLM with second account
Sorry in advance if this has already been addressed!! New, clean, install of Windows 7 Home Premium 64 bit. Install went remarkably well ( some driver issues) until I got to the step wher I decided to become one with Windows Live Mail. Joined up as needlenoses@ live.ca and entered a second existing account @telus.net. I made sure that the telus.net account was set as default; then the mail started coming in. I will get 10 to 20 posts sent to me at telus.net arrive in the live.ca inbox (checked propertys of them all). Next time I will get 5 mail go into telus account, th...

Series of two non-adjacent cells?
I have a list of daily values. I want to see the slope between two values that are several months apart. How do I define a series of just two cells (D224 and D266) that are non-adjacent (non-sequential?)? Thanks, Mike p.s. When I searched this group for "trendline", I got no hits. When I searched for "trend", I got a lot of hits, including many hits containing the word, "trendline". Isn't that odd? Do you want to calculate it or chart it? Presumably you have dates in related cells (assuming column A). Calculation is easy: =(D266-D224)/(A266-A224) ...

How do I search for the second largest value in the array?
How do I search for the second (or third and so on) largest value in the array either in column or row? =LARGE(A:A,2) =LARGE(A:A,3) =LARGE(1:1,2) =LARGE(1:1,3) -- David Biddulph "Golf" <Golf@discussions.microsoft.com> wrote in message news:241155EE-30CA-4E7C-8E8C-2215F1523919@microsoft.com... > How do I search for the second (or third and so on) largest value in the > array either in column or row? Assume that you would like to get the result from A column Data. Column:- This will get the Largest number from A Column =LARGE(A:A,1) Similar ...

Synchronizing two computers
I built a new computer a few months ago and set it up with Windows 7 as the OS. I still have my old computer with Windows XP Pro, SP3 on it. The old computer has my old accounting and tax records under MS Money and H&R Block Tax Prep, and all my personal stuff like image files, Word documents, etc., on it. The new computer has Intel Quicken and Turbo Tax. I have imported my accounting files into Quicken on the new computer but have kept a parallel set of these records going on my old computer...just in case something went kaput. I kept the old computer alive and active until I ...

conditional formatting graph
Is it possible to do conditional formatting on a bar graph? I need one bar to change to red if it is below the national average or stay green if it's above. I have found many postings on how to do something like this in Excel but nothing on how to do it in Access. Any help would be really appreciated. Thanks Becky ...

Setting up two users in Outlook
How do you set up multiple users in Outlook (I don't want to share the calendar or contacts). I tried creating a new .pst file, but when I opened it my contacts and calendar information appeared also on the new file. When I deleted a contact, it deleted it in my .pst file as well as the new one. Thanks. What version of Outlook are you using, and if 98 or 2000, what mail support mode? If you're not sure, look at the second line of Help | About Microsoft Outlook -- it should say "Internet Mail Only" or "Corporate/Workgroup". (Outlook 97, 2002, and 2003 don'...

Outlook only syncs TWO MONTHS past appointments
I use Outlook for contacts, calendar, tasks and notes. Not for email. I need to move my data among four different PCs and it MUST be current and accurate on my PDA (iPaq 3715, PPC2002SE). What I'd like to do is keep my PDA current and just sync it (via ActiveSync 3.8) to update various instances of Outlook. When I get to an outdated Outlook I rename Outlook.pst, create a new (blank, empty) Outlook.pst file and do a sync. This works fine with ONE EXCEPTION. Outlook syncs all contacts, notes and tasks but ONLY SYNCS TWO MONTHS' OF PAST APPOINTMENTS. Why??? I turned off Auto...

Can I combine two of or more files of the same type and layout
I would like to combine several spreadsheets and then do a sort of both of them. Is this possible. Do you mean several sheets within one workbook (.xls file) or do you mean sheets that are currently in multiple workbooks? I ask because often people use one term when they mean the other, we give them an answer and it turns out to be wrong. For 'sheets' you might look at: http://excel.tips.net/Pages/T003005_Condensing_Multiple_Worksheets_Into_One.html or http://www.vbaexpress.com/kb/getarticle.php?kb_id=151 "GRG" wrote: > I would like to combine ...

How do I chart two separate sets of x and corresponding y values .
I would like to chart two sets of x and y values with each set of x values corresponding to its own set of dependant y values. The two sets of x values for the ordinate are entirely different. It seems like I can only chart two sets of data on the same graph with the same values for x and two separate sets of dependant values for y. Is there any way possible to get around this problem? Select your first set of X&Y data, Insert/ Chart (Chart type XY, & choose an appropriate sub-type)/ other options as appropriate. Now select your second set of X&Y data, Copy, select your c...

Conditional Format question #2
SG93IGNhbiBJIGNyZWF0ZSBhIGZvcm1hdCBmb3IgdGhlIGNlbGxzIHdoaWNoIGNvbnRhaW5zIGEg Y2VydGFpbiBzdWJzdHJpbmc/ > How can I create a format for the cells which contains a certain > substring? Select col A (A1 active), then apply the CF using Formula Is: =ISNUMBER(SEARCH("diesel fuel",A1)) Format to taste > ok out Replace SEARCH with FIND if you need it to be a stricter, case sensitive search -- Max Singapore http://savefile.com/projects/236895 Downloads:19,000, Files:362, Subscribers:62 xdemechanik --- Cells being in column A use Format>Conditional formatting with this ...

Lookup query value from Form
Hello. I have a form that I would like to lookup values in several taxt boxes. The values I want displayed are from several different Sum Queries. Example: I want to pull Total Working Blance from a query and monthly rent revenue from another query. The values all come from different tables. Is this possible? Thanks for the help. you can use a DLookup() function to retrieve the queries' values. read up on the function in Access Help, so you'll understand how it works. then add an expression to each textbox control's ControlSource property, as =DLookup("MyField", ...

Autofilter shows no results, but there should be because there are matches
the column that I am trying to use autofilter on has nothing but numbers in it. But when I do a custom filter for a number that begins with '5' (for example), it shows no results. Even though there are plenty of numbers that start with '5'. I do the same excercise on another worksheet that is formatted the same, and it works fine. If I go back to the non-working worksheet and retype the number in each cell the autofilter will work. WHY OH WHY is this so? That happened to me whenever I imported data from a particular database. On import, somehow a little 1/2 space was put i...

Compare two files and update data from another file base on words in a cell separated by commas
I have two file with several colomns. I need to compare two Col B fileA Col B of FileB as shown in example. http://spreadsheets.google.com/ccc?key=0AgUVfFOnkiaKdFBiNDFLamcybXdhW... Each col have about 1000 rows. Each row contains thousands of words and phrases separated by Comma. As you can see from example, my data has soo many words and phrases separated by comma in each row of two colomn A and B. ============== i Need to merge data of corresponding row from COLA$FileB TO corresponding row of COLA$FileB Also merge data of corresponding row from COLB$FileB TO correspondin...

sort column by last digit
Lets say I have a column like: 12435G 19995 188884G 188465 277745 etc etc etc ...and I want to sort it so that the ones that end with the letter G are isolated. Is there a way to do this? -- Kepf ------------------------------------------------------------------------ Kepf's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30611 View this thread: http://www.excelforum.com/showthread.php?threadid=502969 In a helper column, add =IF(RIGHT(A1)="G","G","") then sort by the helper column. -- HTH Bob Phillips (remove nothere from...