Lookup Formula: Return 1st match, then 2nd match, then 3rd match

I have a spreadsheet that looks like the following. Tab 1. Col A=Name. Col B 
= Task. 

A               B
Bob         Reconcile Cash
Tim         Do Sales Report
Bob          Create presentation 
Tim         Prepare financial statements
Bob         Hire staff person

Now on tab two, I want to create another list that pulls all the tasks 
together by person. For example, Tab 2 would look like this: 
Bob        Reconcile Cash
Bob        Create presentation 
Bob        Hire staff person 
Tim        Do Sales Report
Tim        Prepare financial statement. 

I realize I can just sort this list by name but I don't want Tab 2 to work 
like that. I'm frequently shuffling tasks between people so I want Tab 2 to 
automatically update as I'm moving tasks around between people. I want to 
have some form of lookup formula in Tab 2 that says "get the first task for 
Bob and return it," and then "get the second task for Bob and return it." 
0
Utf
12/10/2009 8:58:01 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
931 Views

Similar Articles

[PageSpeed] 36

Would one of these layouts be ok...

Bob...Reconcile Cash...Create presentation...Hire staff person
Tim...Do Sales Report...Prepare financial statement.

Or...

Bob.................................Tim
Reconcile Cash................Do Sales Report
Create presentation..........Prepare financial statement
Hire staff person

-- 
Biff
Microsoft Excel MVP


"Scott" <Scott@discussions.microsoft.com> wrote in message 
news:5F658D56-F407-4B42-9C4E-12C1EACBE56A@microsoft.com...
>I have a spreadsheet that looks like the following. Tab 1. Col A=Name. Col 
>B
> = Task.
>
> A               B
> Bob         Reconcile Cash
> Tim         Do Sales Report
> Bob          Create presentation
> Tim         Prepare financial statements
> Bob         Hire staff person
>
> Now on tab two, I want to create another list that pulls all the tasks
> together by person. For example, Tab 2 would look like this:
> Bob        Reconcile Cash
> Bob        Create presentation
> Bob        Hire staff person
> Tim        Do Sales Report
> Tim        Prepare financial statement.
>
> I realize I can just sort this list by name but I don't want Tab 2 to work
> like that. I'm frequently shuffling tasks between people so I want Tab 2 
> to
> automatically update as I'm moving tasks around between people. I want to
> have some form of lookup formula in Tab 2 that says "get the first task 
> for
> Bob and return it," and then "get the second task for Bob and return it." 


0
T
12/10/2009 9:28:45 PM
The second layout would work where you showed: 

Bob.................................Tim
Reconcile Cash................Do Sales Report
Create presentation..........Prepare financial statement
Hire staff person


"T. Valko" wrote:

> Would one of these layouts be ok...
> 
> Bob...Reconcile Cash...Create presentation...Hire staff person
> Tim...Do Sales Report...Prepare financial statement.
> 
> Or...
> 
> Bob.................................Tim
> Reconcile Cash................Do Sales Report
> Create presentation..........Prepare financial statement
> Hire staff person
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "Scott" <Scott@discussions.microsoft.com> wrote in message 
> news:5F658D56-F407-4B42-9C4E-12C1EACBE56A@microsoft.com...
> >I have a spreadsheet that looks like the following. Tab 1. Col A=Name. Col 
> >B
> > = Task.
> >
> > A               B
> > Bob         Reconcile Cash
> > Tim         Do Sales Report
> > Bob          Create presentation
> > Tim         Prepare financial statements
> > Bob         Hire staff person
> >
> > Now on tab two, I want to create another list that pulls all the tasks
> > together by person. For example, Tab 2 would look like this:
> > Bob        Reconcile Cash
> > Bob        Create presentation
> > Bob        Hire staff person
> > Tim        Do Sales Report
> > Tim        Prepare financial statement.
> >
> > I realize I can just sort this list by name but I don't want Tab 2 to work
> > like that. I'm frequently shuffling tasks between people so I want Tab 2 
> > to
> > automatically update as I'm moving tasks around between people. I want to
> > have some form of lookup formula in Tab 2 that says "get the first task 
> > for
> > Bob and return it," and then "get the second task for Bob and return it." 
> 
> 
> .
> 
0
Utf
12/10/2009 10:31:01 PM
You could use this formula in C1 of Tab1:

=3DA1&"_"&COUNTIF(A$1:A1,A1)

and copy this down as far as you need to. It will give you something
like this:

Bob_1
Tim_1
Bob_2
Tim_2
Bob_3

and so on, i.e. you have a sequential count of Bob's tasks, and of
Tim's tasks, and this gives you a unique reference. In your other
sheet you can then make use of an INDEX/MATCH combination, also using
the ROW() function arranged however you wish.

Hope this helps.

Pete

On Dec 10, 8:58=A0pm, Scott <Sc...@discussions.microsoft.com> wrote:
> I have a spreadsheet that looks like the following. Tab 1. Col A=3DName. =
Col B
> =3D Task.
>
> A =A0 =A0 =A0 =A0 =A0 =A0 =A0 B
> Bob =A0 =A0 =A0 =A0 Reconcile Cash
> Tim =A0 =A0 =A0 =A0 Do Sales Report
> Bob =A0 =A0 =A0 =A0 =A0Create presentation
> Tim =A0 =A0 =A0 =A0 Prepare financial statements
> Bob =A0 =A0 =A0 =A0 Hire staff person
>
> Now on tab two, I want to create another list that pulls all the tasks
> together by person. For example, Tab 2 would look like this:
> Bob =A0 =A0 =A0 =A0Reconcile Cash
> Bob =A0 =A0 =A0 =A0Create presentation
> Bob =A0 =A0 =A0 =A0Hire staff person
> Tim =A0 =A0 =A0 =A0Do Sales Report
> Tim =A0 =A0 =A0 =A0Prepare financial statement.
>
> I realize I can just sort this list by name but I don't want Tab 2 to wor=
k
> like that. I'm frequently shuffling tasks between people so I want Tab 2 =
to
> automatically update as I'm moving tasks around between people. I want to
> have some form of lookup formula in Tab 2 that says "get the first task f=
or
> Bob and return it," and then "get the second task for Bob and return it."

0
Pete_UK
12/11/2009 2:30:54 AM
Try this...

Data on Sheet1 in the range A2:B6

Sheet2:

A1:B1 = Employee names = Bob, Tim

In the formula:

Emp (Employee) refers to Sheet1$A$2:$A$6
Task refers to Sheet1$B$2:$B$6

Enter this array formula** on Sheet2 in cell A2:

=IF(ROWS(A$2:A2)>COUNTIF(Emp,A$1),"",INDEX(Task,SMALL(IF(Emp=A$1,ROW(Task)),ROWS(A$2:A2))-MIN(ROW(Task))+1))

** array formulas need to be entered using the key combination of 
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT 
key then hit ENTER.

Copy acrosss to B2 then down until you get a full row of blanks.

-- 
Biff
Microsoft Excel MVP


"T. Valko" <biffinpitt@comcast.net> wrote in message 
news:%23xtKC$deKHA.2780@TK2MSFTNGP05.phx.gbl...
> Would one of these layouts be ok...
>
> Bob...Reconcile Cash...Create presentation...Hire staff person
> Tim...Do Sales Report...Prepare financial statement.
>
> Or...
>
> Bob.................................Tim
> Reconcile Cash................Do Sales Report
> Create presentation..........Prepare financial statement
> Hire staff person
>
> -- 
> Biff
> Microsoft Excel MVP
>
>
> "Scott" <Scott@discussions.microsoft.com> wrote in message 
> news:5F658D56-F407-4B42-9C4E-12C1EACBE56A@microsoft.com...
>>I have a spreadsheet that looks like the following. Tab 1. Col A=Name. Col 
>>B
>> = Task.
>>
>> A               B
>> Bob         Reconcile Cash
>> Tim         Do Sales Report
>> Bob          Create presentation
>> Tim         Prepare financial statements
>> Bob         Hire staff person
>>
>> Now on tab two, I want to create another list that pulls all the tasks
>> together by person. For example, Tab 2 would look like this:
>> Bob        Reconcile Cash
>> Bob        Create presentation
>> Bob        Hire staff person
>> Tim        Do Sales Report
>> Tim        Prepare financial statement.
>>
>> I realize I can just sort this list by name but I don't want Tab 2 to 
>> work
>> like that. I'm frequently shuffling tasks between people so I want Tab 2 
>> to
>> automatically update as I'm moving tasks around between people. I want to
>> have some form of lookup formula in Tab 2 that says "get the first task 
>> for
>> Bob and return it," and then "get the second task for Bob and return it."
>
> 


0
T
12/11/2009 5:50:01 AM
Reply:

Similar Artilces:

Table relationships and lookups
Hi guys, I may be a little over my head, I've had some experience in creating simple access db's. however this one will be extremely complicated as far as I can tell. Some backround info - i've got an excel spreadsheet currently that i would like to convert to Access. The spreadsheet does multiple lookups and calucations. This is for a Soccer club that i run to maintain roster information, dollars, scheduling and stats. I'm currently working on the scheduling pience. Here's what I have so far. tables. Club - Lists the teams in the club, home field name and ...

SQL query / Formula for Reorder Point?
Is it possible to issue an SQL command(s) that would: 1. Set the Restock Level for all items to be equal to the current on-hand quantity for each item. 2. Set the Reorder point for all items to be equal to 1/3 of that items Restock Level. I'm currently doing this manually, or using a formula in excel when items are initially imported into the DB, but there are still several thousand that need to be retro-fitted like this... Thanx in advance! -=RFM=- UPDATE Item SET RestockLevel=(Quantity - QuantityCommitted) UPDATE Item SET ReorderPoint=ROUND(RestockLevel/3,0) - Evan Culver Ne...

Invoice lookup by paid check number
I frequently have vendors call me asking for information about what invoices were paid by a check number. Is ther an easy way to look this information up? -- Rodger You could go to Inquiry>Purchasing>Transactions by Document, put your check number in the 'from' and 'to' fields. Once the document is displayed in the scrolling window zoom back on the 'Unapplied Amount' field. Viola! the documents paid by the selected payment are listed. Unfortunately there isn't a print icon on this inquiry window, but I think it's the information you wanted. &quo...

Excel Formulae
how can a hundreds of numbers in a worksheet be adjusted by the same percentage Ray, You can use Paste Special to do this. For example, to increase every number by 15%, enter 1.15 in some empty cell and copy that cell. Then select the cells that you wish to change. Go to the Edit menu, choose Paste Special, and there choose the Multiply option. -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com chip@cpearson.com "Ray G" <ray_garthwaite@hotmail.com> wrote in message news:1ca701c38855$de4fdf40$a001280a@phx.gbl... > how can a hundreds of numbers in a w...

Simple formula
Hi, I'm a new to Excel so please excuse my ignorance in advance. I have data in Column C that I need to multiply with Column D. I know (for example) I can get a result by placing the formula: =sum(C3*D3) in cell E3. But, I have very long columns of data and I don't want to type in =sum(C4*D4)in cell E4, =sum(C5*D5) in cell E5 etc. My question is; can I use a wildcard for the column number and then just paste this formula in each E cell? Thanks On Fri, 22 Jul 2005 14:02:51 +0100, PigPOg <simon@capella.co.uk> wrote: >Hi, I'm a new to Excel so please excuse my ignoranc...

Handling #REF returned from GETPIVOTDATA
A GETPIVOTDATA function returns #REF, but when I change that formula to =ISREF(GETPIVOTDATA... I get FALSE. I would have expected TRUE. Why the difference This is part of the development of a formula to return a zero when GETPIVOTDATA returns #REF. Thanks Laurence Lombard Try =if(iserror(getpivotdata(.... =isref() will return true if you pass it an address: =isref(a1) (since A1 is an address) I don't recall ever seeing this used on a worksheet. I bet that this may have been useful in the older XLM style macros (just a guess). Laurence Lombard wrote: > > A GETPIVOTDATA fu...

Lookup
Q103 Q102 Q202 Q302 Q402 Q103 Q203 How can I lookup the Q103 in the row above and then have it pull the number to the right one cell (Q203)? thanks If I understand correctly =INDEX(A2:F2,MATCH(A1,A2:F2,0)+1) where q103 is in a1 and q102-q203 is in a2-f2 Lance >-----Original Message----- >Q103 > > >Q102 Q202 Q302 Q402 Q103 Q203 > > >How can I lookup the Q103 in the row above and then have >it pull the number to the right one cell (Q203)? thanks >. > matt wrote: > Q103 > > > Q102 Q202 Q302 Q402 Q...

Help with LOOKUP function
This function is in a workbook with 2 sheets. It _almost_ works perfectly. These "C" columns in two different sheets '2005-2006'!C:C,'2004-2005'!C:C, contain names of people. The D column in one of the sheets - '2004-2005'!D:D - contains a date associated with the person's name from the C column of 2004-2005 sheet. This formula is in the "D" column of Sheet 2005-2006. =LOOKUP('2005-2006'!C:C,'2004-2005'!C:C,'2004-2005'!D:D) The concept is for the formula to lookup the value (person's name) in column C of 2005-2006 a...

current user returns admin
I would like to log in one of the databases the username of the person who is making changes to a record in a database, but I noticed that access 2207 doesn't have user level security anymore and the currentuser method just returns admin. Is there a way to do this? On Jan 3, 10:16 am, icccapital <icccapi...@discussions.microsoft.com> wrote: > I would like to log in one of the databases the username of the person who is > making changes to a record in a database, but I noticed that access 2207 > doesn't have user level security anymore and the currentuser method ju...

A Lookup function does not work
Hi, This is my first posting. I am using Exel 2000. I have 2 separate spreadsheets that have some similar columns but not all of the data in the similar columns is the same. What I want to do is take column A in spreadsheet#1 and find this same value in Column B in Spreadsheet#2 and then insert into column 3 in spreadsheet #1 a value from a different column in spreasheet #2 that corresponds to the row in which the value was looked up in Column B in spreadsheet#2. What I am doing is comparing 2 different inventory files that have stock codes in columns and quantities in another column, but n...

Rookie Incremental formula help
I have a simple sheet with totals from (b2)*(d2). Total = [Formula] When I drag the incremental formula down across 3 sheets and hiding th zero values, printing still prints 3 blank pages. Is there a way to fill in the next incrementing formula [(b2)*(d2) when the calulation is made? I want to keep from having 300 growin hidden formulas, I'd rather try to have them incrimentaly auto fill s my print outs wont have 20 extra blank pages. Many thanks -- RubberSou ----------------------------------------------------------------------- RubberSoul's Profile: http://www.excelforum.com/me...

Formula partial result displayed in cell
My formula =D3*0.0275+850 displays the '850' in the cell without yet having a value in D3. I know how to avoid having zero values displayed, but how do I tell Excel I don't want a partial result displayed. Try this formula IF(D3<>"",D3*0.0275+850,"" -- daddylongleg ----------------------------------------------------------------------- daddylonglegs's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3048 View this thread: http://www.excelforum.com/showthread.php?threadid=50141 ...

return cell reference in a table based upon given lookup criteria
Is there a way to return the cell reference, or column/row coordinates, of a cell within an array or table by providing lookup criteria? Perhaps something like this: For a table of value in A1:E10 F1: (the value to find) G1: =ADDRESS(MAX((A1:E10=F1)*ROW(A1:E10)),MAX((A1:E10=F1)*COLUMN(A1:E10))) Note: Commit that array formula by holding down the [Ctrl][Shift] keys and press [Enter]. That formula returns the address of the 1st cell containing the value in F1, or #VALUE! if there is no match. Am I on the right track here? *********** Regards, Ron XL2002, WinXP-Pro "Travis" ...

Advanced Lookups
Is there any way to make an advanced lookup the default lookup? so you don't have to always choose that option when doing a lookup? Thanks for any help. Tracey D Advanced lookups ARE the default unless you've done something to make it now so. There isn't any way to "choose" the option when doing a lookup that I know of unless you have some type of customization (easy to do) that would give the user an option. patrick dev support -- This posting is provided "AS IS" with no warranties, and confers no rights. "Tracey D" <...

Cell Formula reference to cell Based On third Cell Content #2
I want to build a formula in one cell that calls a second based on the numeric value in a third. Specifically, Say the frst cell is F10. I want it to look up the number in F1, and then if F1=1, F10=E9*something if F1=2, F10=D9*something if F1=3, F10=C9* something etc. Hi! I notice that you use "etc" which means there are more. Exactly how many more? That number will determine the best way to approach this. Biff >-----Original Message----- >I want to build a formula in one cell that calls a second based on the >numeric value in a third. > >Specifically, Say...

Excel IF Formula
I have a spreadsheet with 4 cells that 1 of the 4 will have a calculated value in it. I want to take the numeric value in the cell that has a value and place it into another cell for information. Following is the string I have written but I am doing something wrong. =IF(E25>1, E25,IF(E27>1, E27, If(E29>1, E29,IF(E31>1, E31)))),"ERROR" Any help would be greatly appreciated. -- Thanks GeneI This is your "corrected" formula: =IF(E25>1,E25,IF(E27>1,E27,IF(E29>1,E29,IF(E31>1,E31,"ERROR")))) Micky "GeneI" wro...

Calling employee lookup from button through VBA code
Dear All, Can anyone show me how to call an existing GP employee lookup from a button of a modified form through VBA code. Thanks in advance. -- Developer Hi, If I'm understanding the question - you need to add the lookup button to your project and make sure your project provides that it runs on the modified form. Leslie "Dexdev" wrote: > Dear All, > > Can anyone show me how to call an existing GP employee lookup from a button > of a modified form through VBA code. > > > Thanks in advance. > > -- > Developer Hello Dexdev As per...

Vendor Lookup
One doing the vendor lookup - one user sees the 'show details' information upon lookup; other user sees the vendor list and needs to clik on the show details - how do you get the show details window to be the default option you see. Thansk! Check for full stops/periods/dots on the window title bar before or after the window name. It is possible to use VBA or modifier to open the details automatically. David Musgrave [MSFT] Escalation Engineer - Microsoft Dynamics GP Microsoft Dynamics Support - Asia Pacific Microsoft Dynamics (formerly Microsoft Business Solutions) http://www...

An array formula to sum row max
I have a data range of 1000 rows and 20 columns. I need a sum of the maximum number of each rows. I usually add another column to calculate row max. But is it possible to eliminate this intermediate step and just use a single cell array formula to return the sum of row maxes? Thanks -- ucamms ------------------------------------------------------------------------ ucamms's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19151 View this thread: http://www.excelforum.com/showthread.php?threadid=382107 Assuming that A1:T1000 contains your data, try... =SUMPRO...

Formulas or not ??
Good morning guys, I woant a formula which when referring to another cell verifies whether this cell contains a numerical value or otherwise a normal numerical number Is this possible ? Thanks in dvance, Brian Hi Brian, > I woant a formula which when referring to another cell > verifies whether this cell contains a numerical value or > otherwise a normal numerical number > =IF(ISNUMBER(A1),"A1 contains a number","A1 Does not contain a number") Regards, Jan Karel Pieterse Excel MVP www.jkp-ads.com Hi Brian! I'm not sure whether I'm interpre...

Copying Formulas In Pivot Table
Working in a pivot table where I have formulas pulling from other fields within the pivot table, can I copy the formula to range of cells without having to enter the formula in each cell? I keep getting an error that I cannot copy/paste or drag the formula across the table. Thanks! ...

Lookup #4
I am trying to label my bank info that I import into Excel. I have a column with the charge amount and a column with the recipient. I think a lookup table would work for this, but I need to have it check for wildcards. For example, the grocery chain is numbered here, so one might be GROCER 5454 and one may be GROCER 8724, so I need the function to look for GROCER*, right? Also, if it does not fit any category, how can I get it to use "Other"? Do I make that one just "*"? Mine is not working very well. Maybe my syntax is wrong. Any ideas? TIA Is there anyway you can st...

Filter message by reverse domain lookup
Hello all, I am kinda new to Exchange and was wondering if there was a function built into Exchange 2000 that would "look" at the source domain of the email message and then perform a "reverse" DNS lookup to verify that the domain actually exists and if it doesn't then have Exchange discard the email without sending an NDR? Is this possible strait out of the box? It seems like I remember iMail gateway having this feature. I am trying to filter out some of the spam that fills up my users inboxes. Thanks in advance. Jeremy jsteger@bellsouth.net (Jeremy Steger) wrote...

Can you change the default lookup from Account to Contact?
Is it possible to set the lookup for a customer field to default on "contacts" rather then "accounts?" We do more business with contacts so it is a pain to have to change this all the time. there is no supported way to do this in the current release -- John O'Donnell Microsoft CRM MVP http://www.microsoft.com/BusinessSolutions/Community/CRMFaqLanding.aspx "Martin Flaherty" <mflaherty@techpg.com> wrote in message news:ucdNM74uEHA.3416@TK2MSFTNGP09.phx.gbl... > Is it possible to set the lookup for a customer field to default on > "contac...

Simply Formula
Is there a simple formula approach to trimming numbers? Our full Grant Number in one system appears as Example: 1 F32 AI540542003A1 and in the other system it appears 54054 Also, can you explain the formula below? =IF(H30<>"CA",TRIM(F30)&" "&TRIM(G30)&" "&TRIM(H30)&RIGHT (LEFT(TRIM(B30),14),5)&TRIM(D30)&TRIM(K30),TRIM(F30) &" "&TRIM(G30)&" "&TRIM(H30)&RIGHT(LEFT(TRIM(B30),14),6) &TRIM(D30)&TRIM(K30)) Thanks Again for all your Help!!! Hi, For trimming numbers there are a number...