Lookup #3

Hi All 

I have a table that contains the following infromation in A2:FT6010
I need to lookup the information in FT witch is column 175 that matches
critiera from A (date) and Name 

If 1/2/05 and Kerry = 94.96%
EG
A[INDENT                     ]B[INDENT          ]FT
1/02/2005[INDENT]	Mathew[INDENT]100.00%
1/02/2005[INDENT]	Kelly[INDENT      ]98.25%
1/02/2005[INDENT] Paul[INDENT       ]92.12%
1/02/2005[INDENT]	Kerry[INDENT     ]94.96%
1/02/2005[INDENT ]Rick[INDENT        ]98.18%
2/02/2005[INDENT]	Mathew[INDENT ]99.85%
2/02/2005[/NDENT ]Kelly[INDENT       ]75.12%

Thanks


-- 
mathewheys
------------------------------------------------------------------------
mathewheys's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25093
View this thread: http://www.excelforum.com/showthread.php?threadid=386337

0
7/12/2005 5:02:53 AM
excel 39879 articles. 2 followers. Follow

2 Replies
330 Views

Similar Articles

[PageSpeed] 19

One way ..

Assuming the source data is in Sheet1, with dates in col A, names in col B,
percentages in col FT, data from row2 down

In Sheet2

With the dates in col A, names in col B, form row1 down,
put in the formula bar for C1 and array-enter
(i.e. press CTRL+SHIFT+ENTER):

=IF(ISNA(MATCH(1,(Sheet1!$A$2:$A$100=A1)*(Sheet1!$B$2:$B$100=B1),0)),"",INDE
X(Sheet1!$FT$2:$FT$100,MATCH(1,(Sheet1!$A$2:$A$100=A1)*(Sheet1!$B$2:$B$100=B
1),0)))

Format C1 as percentage, fill down until the last row of data in cols A and
B

If you have in A1: 2/1/05, in B1: Kelly, then C1 will return: 98.25%
Unmatched cases, if any, will return blanks: ""

Adapt the ranges to suit ..
--
Rgds
Max
xl 97
---
GMT+8,  1� 22' N  103� 45' E
xdemechanik <at>yahoo<dot>com
----
"mathewheys" <mathewheys.1s1j6b_1121144705.0854@excelforum-nospam.com> wrote
in message news:mathewheys.1s1j6b_1121144705.0854@excelforum-nospam.com...
>
> Hi All
>
> I have a table that contains the following infromation in A2:FT6010
> I need to lookup the information in FT witch is column 175 that matches
> critiera from A (date) and Name
>
> If 1/2/05 and Kerry = 94.96%
> EG
> A[INDENT                     ]B[INDENT          ]FT
> 1/02/2005[INDENT] Mathew[INDENT]100.00%
> 1/02/2005[INDENT] Kelly[INDENT      ]98.25%
> 1/02/2005[INDENT] Paul[INDENT       ]92.12%
> 1/02/2005[INDENT] Kerry[INDENT     ]94.96%
> 1/02/2005[INDENT ]Rick[INDENT        ]98.18%
> 2/02/2005[INDENT] Mathew[INDENT ]99.85%
> 2/02/2005[/NDENT ]Kelly[INDENT       ]75.12%
>
> Thanks
>
>
> --
> mathewheys
> ------------------------------------------------------------------------
> mathewheys's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=25093
> View this thread: http://www.excelforum.com/showthread.php?threadid=386337
>


0
demechanik (4694)
7/12/2005 5:38:26 AM
Hi!

Try this:

Enter the criteria in a couple of cells:

A1 = 1/02/2005
B1 = Kerry

=SUMPRODUCT(--(A2:6010=A1),--(B2:B6010=B1),FT2:FT6010)

Biff

"mathewheys" <mathewheys.1s1j6b_1121144705.0854@excelforum-nospam.com> wrote 
in message news:mathewheys.1s1j6b_1121144705.0854@excelforum-nospam.com...
>
> Hi All
>
> I have a table that contains the following infromation in A2:FT6010
> I need to lookup the information in FT witch is column 175 that matches
> critiera from A (date) and Name
>
> If 1/2/05 and Kerry = 94.96%
> EG
> A[INDENT                     ]B[INDENT          ]FT
> 1/02/2005[INDENT] Mathew[INDENT]100.00%
> 1/02/2005[INDENT] Kelly[INDENT      ]98.25%
> 1/02/2005[INDENT] Paul[INDENT       ]92.12%
> 1/02/2005[INDENT] Kerry[INDENT     ]94.96%
> 1/02/2005[INDENT ]Rick[INDENT        ]98.18%
> 2/02/2005[INDENT] Mathew[INDENT ]99.85%
> 2/02/2005[/NDENT ]Kelly[INDENT       ]75.12%
>
> Thanks
>
>
> -- 
> mathewheys
> ------------------------------------------------------------------------
> mathewheys's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=25093
> View this thread: http://www.excelforum.com/showthread.php?threadid=386337
> 


0
biffinpitt (3172)
7/12/2005 5:40:10 AM
Reply:

Similar Artilces:

multiple calendars #3
I have a user that somehow ended up with multiple calendars. All of his information is in the second calendar. I would like to delete the first calander but it won't let me. How can I delete this or at least move all of his information from the second one to the first, so I can delete the second one? -- Brandon Baker, A+, Net+, MCSA, MCSE Brandon Baker <brandonb@allegiancecu.org> wrote: > I have a user that somehow ended up with multiple calendars. All of > his information is in the second calendar. I would like to delete the > first calander but it won't let ...

Invest in our children- Investment Opportunity !!! #3
INVESTMENT OPPORTUNITY! Fork It Over! a Ground Floor Unique Dimension & Concept In the Children's Entertainment Market... For More information http://www.kidseyeview.us ...

Work Orders #3
Is there a way to add "Reference #" to the Detailed work order report? Also, is there a way to add the Due Date to the cutomer receipt for work orders? -- Thanks, joel Unfortunately the Work Order functionality in RMS is very limited and isn't really meant to do "work order management". If you're a repair shop then you will still need to keep track of your workload the same way you did before. FYI, I'm currently developing a work order add-in for RMS which is directed as repair shops. I was hoping to have it completed by now, but it's still a cou...

CRM 3.0 Upgrade Advisor
Just a quick note of an observation when running the UA. Given that my MS SQL 2000 needed to be upgraded to sp4, when the UA was run, the app would get about 75 % complete then hang. With the SP4 installed, the UA runs successfully. Thanks for all the good information on this site. -- Jon von ...

Import Problem #3
Using Excel 2003, my problem is to import from a csv file into Excel. Easy enough, except I require the top, leftmost cell to be in E10 not A1. There are a lot of text entries, and formulas already in place, and avoiding redoing those everytime a download and import is ordered will be a great enhancement to efficient operations. Thanks Pepper What about using "Replace" from the "Edit" menu to fix the formulas that call A1 and replace with E10? Might not be quite that simple, but could save a bunch of time. -- If my posting was helpful, please click the "Yes&...

Can't install DMF 3.0
Dear All, Please help me on this situation: I can't install DMF, the error message is: "Fail to install the required database tables. Error: Specified owner name 'DBO' either does not exist or you do not have permission to use it. I used the same user domain adminstrator as install MS CRM 3.0 to install DMF. It's appricated if any help. Regards, Duc I think there's a bug in DMF. The collation of my SQL Server 2003 is: Latin1_General_Bin. This means case sensitive. So I have tried something: 1) I tried to change from 'DBO' (upper case) to 'dbo' ...

Transaction Matching #3
All of a sudden, everytime I download recent transactions from my Bank, Money duplicates the entry. Furthermore, if Money associates the transaction to a future occurence of an event, it will not let me change it. Lastly, if I try to match a downloaded transaction to one that I have already entered, Money will not list it in the "Change Transaction Matching" screen. Help! In microsoft.public.money, stiglo wrote: >All of a sudden, everytime I download recent transactions from my Bank, Money >duplicates the entry. Furthermore, if Money associates the transaction to a ...

lookup question
I am having trouble figuring out how to look something up in a list based on conditions. For example I have: Store # State Open Date 1 ND 1/2/03 2 SD 2/4/03 in a list with approx 350 records which all have this same info. I need to create another table that shows which stores opened in which state in each month. So, I need to say if the open date is in January (or between Jan 1 & Jan 31) & the state is ND then I need the cell the formula is in to read 1. Basically if the op...

Business Data Lookup Snap-in Microsoft CRM
Hi All ... Does anybody know "Business Data Lookup Snap-in" ??? I just installed it ... the BDL Snap-in buttons appear in the tool bar of Word (Office 2003) ... but when I click on it nothing append ... A "Documents Action" window should open at the right side of the word document. Anybody can give me a clue ??? Tanks ... Y'all ...

Batch Not Posting #3
Hello all, we have an issue where when we attempt to post from several different modules it gets so far then kills great plains. we have to go into the batch recovery run the batch recovery at which time it will print the reports them GP dies again. then we have to go back into batch recovery and hit cancel on all the reports and it will them post the batch. TIA Brian, The problem might be your Reports Dictionary. Try recreating your reports dictionary and try it again. You can recreate the reports dictionary by two ways: 1 Option 1. Create a package file of all your customizatio...

Conditional Formatting
I'd like to conditionally format the cells in column C based on the cells in Column A. I can't use the the wizard becasue I need about six conditions and the wizard allows only three. Example, if A2 = 2, then C2 = GREEN ; IF A3 = 4, then C3 = Red; etc. Suggest using select case in vba -- Don Guillett SalesAid Software donaldb@281.com "Bdavis" <anonymous@discussions.microsoft.com> wrote in message news:1e1d01c4b51b$d8dc09a0$a601280a@phx.gbl... > I'd like to conditionally format the cells in column C > based on the cells in Column A. I can't use th...

CRM 3.0 integration with Project Server?
Does anyone know if CRM 3.0 will integrate with MS Project Server? It seems to me that this would make sense for the service side of CRM. The global enterprise resources are already there with skillsets. Also for a project oriented company technicians that might be working on projects (Project Server) could also be assigned service calls (CRM 3.0), but the technicians schedule would be kept in two seperate locations without integration. Any help would be appreciated. Best Regards, Kevin Out of the box, the answer is NO. They had other fires to kill to get 3.0 out the door. We can alwa...

CRM 3.0 & SQL CAL ?
Hi there, I can't find a clear answer on my following question: Do i need a SQL CAL ( Client Acces License ) for every client that uses CRM ? i hope someone can give me a answer or related info best regards, Christian I believe so. -- Matt Parks MVP - Microsoft CRM "Christian" <Christian@discussions.microsoft.com> wrote in message news:F8A842E9-7CF9-4F7E-9467-248A9D155ADE@microsoft.com... Hi there, I can't find a clear answer on my following question: Do i need a SQL CAL ( Client Acces License ) for every client that uses CRM ? i hope someone can give me...

merging 2 -3 different MS money files into one
hi over a period of testing and doing other things i have created multiple MS Money files mostly with the same accounts and same transactions, the main difference is the transactions that are available in those files, the oldest file has the oldest transactions and newest one has all the new transactions but both contain some common so i want to merge all of them into one so that i can get all transactions from the begining till now. what is the best method to do this. thanks There is no good method. The only method, and not a good one, is File|Export of individual accounts to .QIF ...

how do i make a brochure with 3 pages per sheet in publisher?
File, New, brochure, there are samples, select one and go from there... -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "chris" <chris@discussions.microsoft.com> wrote in message news:41E8C29E-1ED4-4406-A634-15491287AACB@microsoft.com... > In addition to Mary's reply, the following link should help you get started on the design: http://office.microsoft.com/en-us/assistance/HA010563161033.aspx -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com This posting is provided "AS IS"...

TEsting Reverse DNS lookup
Hi, I have enabled reverse dns lookup in my smtp virtual server. How do i test this feature? In news:E88D7CC9-D9E9-4412-B884-61CC0DA8E62C@microsoft.com, Jack Dorson <JackDorson@discussions.microsoft.com> typed: > Hi, > > I have enabled reverse dns lookup in my smtp virtual server. Why? > How do i test this feature? I guess I'd first have to ask, what is it you want to accomplish with this? On Thu, 8 Jun 2006 08:40:02 -0700, Jack Dorson <JackDorson@discussions.microsoft.com> wrote: >Hi, > >I have enabled reverse dns lookup in my smtp virtual s...

Changing from Outlook Express to Outlook #3
I have installed Office 2000 and want to use Outlook as my mail programme. I have tried to import the mail settings as the help advises but get the message could not locate any mail accounts. Outlook express is working fine. I have the mail account in the outlook accounts window but it doesnt seem to know its there?,. Can any one help please I never relied on the importing of the account settings; just do it manually. Sooner or later you'll have to configure it again like when you reinstall your PC and then you won't have OE to import from either. Contact your ISP for your acc...

linked worksheets #3
I have a linked workbook whati need to know can i link another workbook off the linked one? Source>linked book>linked book Thanks Why don't you just try it ? "dakota" <anonymous@discussions.microsoft.com> wrote in message news:62ca01c3e5e5$b00a91a0$a501280a@phx.gbl... > I have a linked workbook whati need to know can i link > another workbook off the linked one? Source>linked > book>linked book > > Thanks ...

OWA on Exchange 2003 #3
Hi All, I have many users on Linux and I am evaluating Exchnage 2003 on 2003 server. Is it possible for these users to delegate access to there calendars via the OWA "client". They do not have outlook full clients. Unfortunately, users will not be able to delegate rights to any of their folders, including the Calendar, through OWA. This would have to be done through an Outlook client. Also, after these rights are set up, OWA users who need to access UserA's calendar would have to type in http://<server>/exchange/<UserA's mailbox alias>/Calendar/?Cmd=conte...

CRM 3.0
This is just a user question. In an Account, in More Addresses I created a Bill To and a Ship To address. Then in a Contact I created and Opportunity. From this Opportunity I then created a Quote. On the quote form on the Addresses tab, I clicked the Lookup Adresses button, checked Bill To, clicked the Lookup button and all I see is the main Account Address, not the two addresses - Bill To and Ship to. Have I done something wrong? Or do I have to retype the Bill To and Ship To addresses in each new Contact as I create it? Thanks! Shauna In V3.0 additional addresses from accounts doe...

LOOKUP function #7
OS =XP Pro Office 3 LOOKUP function, VERY simple formula, just will not work. Is there something special, an unobvious characteristic about this one? Playing back how I am using it : =LOOKUP(x1,x3:x20,y3:y20) where search in column x3 through x20, for value in x1, returns corresponding contents in adjacent column y3 through y20. Simple. Right? Why will it not work? Appreciative, Wayne On Mar 8, 4:58=A0pm, wgd.roam...@verizon.net wrote: > LOOKUP function, VERY simple formula, just will not work. > Is there something special, an unobvious characteristic > about this one? N...

CRM 3.0 install (upgrade from 1.2) issues
When installing crm it goes through checking the required components and installs them. One of them being MDAC. But then it goes through the next few stages of the wizard and confirms the server meets all the requirements. But it falls over saying that MDAC is not installed or is an incorrect version. Now i have checked the mdac on the server using ccchecker and it has all the required components of the compatable version that ms say is V2.6 upwards. The odd thing is when comparing these settings to the setup on the test server they are identical (that installed fine with an upgrade wi...

Batch Processing 1.2 v 3.0
Hello, In 1.2 we were able to perform mass mailings through the batch processing command. I noticed in 3.0, this is not available. What is available is performing mass mailings through Outlook. Is this true? ...

auto fill data into a cell from a lookup table
Does anyone know how to set up a cell formula so that if the data in a cell (such as a county name) is entered, an amount is placed in the cell in the next column? Hi use VLOOKUP to do this say you have your list of countries and amounts on sheet 2 in the range A1:B100 and then on sheet 1 you want to type a country name in cell A1 and have the amount appear in B1 the formula for B1 would be =VLOOKUP(A1,Sheet2!$A$2:$B$100,2,0) which says, look at the value in A1, find it in the first column of the table in Sheet2 and return the associated value from the second column of this table whe...

Lookup for once #2
Thanks Dave for your help. But is there a way that you could input a formula instead of having the advanced filter? Thank you, Kevin ------------------------------------------------------ Maybe Data|Filter|Advanced filter|Unique Records only. See Debra Dalgleish's site for nice instructions. http://contextures.com/xladvfilter01.html#FilterUR Kevin Lin wrote: > > Hi All, > > Need help on the following. > > A V > 1 AAA001 AAA001 > 2 AAA001 BBB001 > 3 AAA001 BBB002 > 4 BBB001 > 5 BBB001 > 6 BBB002 &...