double lookup #2

In Excel2000, I need help with the following formula:

my data is arranged like this ( I can rearrange if needed)..

joe    pete  sue   rating
30      10     5        A
20       5      3        B
10       2      1        C

I then have values for joe, pete, sue. I want to look up the name and then
give me the rating. The numbers may not be exact. For example, I want the
output to look like this ( with the formula looking up the rating)

name amount  rating (formula needed)
joe       11         C
pete       7          B
sue         1          C

Can someone please help me with the formula to find the rating.

Thanks


0
sugah (4)
8/10/2004 11:12:41 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
350 Views

Similar Articles

[PageSpeed] 35

Hi
try the following formula in C2:
=INDEX(OFFSET('sheet1'!$A$2:$A$10,0,MATCH(A2,'sheet1'!$A$1:$C$1,0)),MAT
CH(B2,OFFSET('sheet1'!$A$2:$A$10,0,MATCH(A2,'sheet1'!$A$1:$C$1,0)),-1))


--
Regards
Frank Kabel
Frankfurt, Germany


bg wrote:
> In Excel2000, I need help with the following formula:
>
> my data is arranged like this ( I can rearrange if needed)..
>
> joe    pete  sue   rating
> 30      10     5        A
> 20       5      3        B
> 10       2      1        C
>
> I then have values for joe, pete, sue. I want to look up the name and
> then give me the rating. The numbers may not be exact. For example, I
> want the output to look like this ( with the formula looking up the
> rating)
>
> name amount  rating (formula needed)
> joe       11         C
> pete       7          B
> sue         1          C
>
> Can someone please help me with the formula to find the rating.
>
> Thanks

0
frank.kabel (11126)
8/10/2004 11:18:39 PM
I am still having problems with this formula ( I keep getting #N/A). Can you
please tell me where the data should be. Also, the amounts I am looking up
are not exact. Does this matter?

Thanks again.


"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:uaTICBzfEHA.2848@TK2MSFTNGP10.phx.gbl...
> Hi
> try the following formula in C2:
> =INDEX(OFFSET('sheet1'!$A$2:$A$10,0,MATCH(A2,'sheet1'!$A$1:$C$1,0)),MAT
> CH(B2,OFFSET('sheet1'!$A$2:$A$10,0,MATCH(A2,'sheet1'!$A$1:$C$1,0)),-1))
>
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
>
> bg wrote:
> > In Excel2000, I need help with the following formula:
> >
> > my data is arranged like this ( I can rearrange if needed)..
> >
> > joe    pete  sue   rating
> > 30      10     5        A
> > 20       5      3        B
> > 10       2      1        C
> >
> > I then have values for joe, pete, sue. I want to look up the name and
> > then give me the rating. The numbers may not be exact. For example, I
> > want the output to look like this ( with the formula looking up the
> > rating)
> >
> > name amount  rating (formula needed)
> > joe       11         C
> > pete       7          B
> > sue         1          C
> >
> > Can someone please help me with the formula to find the rating.
> >
> > Thanks
>


0
sugah (4)
8/11/2004 1:51:46 AM
Hi
that the amounts are not exact shoudn't matter (As I'm using -1 as 3rd
parameter in the last MATCH function call. You may try both MATC
functions individually and see which data it does not find. e.g. try:
=MATCH(A2,'sheet1'!$A$1:$C$1,0)

and (if you want to check for Joe)
=MATCH(B2,'sheet1'!$A$2:$C$20,0)

Also I made one mistake in the original formula. Try:
=INDEX(OFFSET('sheet1'!$A$2:$A$10,0,MATCH(A2,'sheet1'!$A$1:$C$1,0)-1),M
ATCH(B2,OFFSET('sheet1'!$A$2:$A$10,0,MATCH(A2,'sheet1'!$A$1:$C$1,0)),-1
))


--
Regards
Frank Kabel
Frankfurt, Germany


bg wrote:
> I am still having problems with this formula ( I keep getting #N/A).
> Can you please tell me where the data should be. Also, the amounts I
> am looking up are not exact. Does this matter?
>
> Thanks again.
>
>
> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> news:uaTICBzfEHA.2848@TK2MSFTNGP10.phx.gbl...
>> Hi
>> try the following formula in C2:
>>
=INDEX(OFFSET('sheet1'!$A$2:$A$10,0,MATCH(A2,'sheet1'!$A$1:$C$1,0)),MAT
>>
CH(B2,OFFSET('sheet1'!$A$2:$A$10,0,MATCH(A2,'sheet1'!$A$1:$C$1,0)),-1))
>>
>>
>> --
>> Regards
>> Frank Kabel
>> Frankfurt, Germany
>>
>>
>> bg wrote:
>>> In Excel2000, I need help with the following formula:
>>>
>>> my data is arranged like this ( I can rearrange if needed)..
>>>
>>> joe    pete  sue   rating
>>> 30      10     5        A
>>> 20       5      3        B
>>> 10       2      1        C
>>>
>>> I then have values for joe, pete, sue. I want to look up the name
>>> and then give me the rating. The numbers may not be exact. For
>>> example, I want the output to look like this ( with the formula
>>> looking up the rating)
>>>
>>> name amount  rating (formula needed)
>>> joe       11         C
>>> pete       7          B
>>> sue         1          C
>>>
>>> Can someone please help me with the formula to find the rating.
>>>
>>> Thanks

0
frank.kabel (11126)
8/11/2004 5:11:06 AM
Thanks again. I am still not getting the ranking. I am still getting N/A. In
sheet1, I have the data in cells A1:D5. In sheet2, I have my lookup area in
A1:C3. With your formula I get:

name amount  rating
joe       11         N/A
pete       7          N/A

thanks




"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:%23qux%23F2fEHA.396@TK2MSFTNGP12.phx.gbl...
> Hi
> that the amounts are not exact shoudn't matter (As I'm using -1 as 3rd
> parameter in the last MATCH function call. You may try both MATC
> functions individually and see which data it does not find. e.g. try:
> =MATCH(A2,'sheet1'!$A$1:$C$1,0)
>
> and (if you want to check for Joe)
> =MATCH(B2,'sheet1'!$A$2:$C$20,0)
>
> Also I made one mistake in the original formula. Try:
> =INDEX(OFFSET('sheet1'!$A$2:$A$10,0,MATCH(A2,'sheet1'!$A$1:$C$1,0)-1),M
> ATCH(B2,OFFSET('sheet1'!$A$2:$A$10,0,MATCH(A2,'sheet1'!$A$1:$C$1,0)),-1
> ))
>
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
>
> bg wrote:
> > I am still having problems with this formula ( I keep getting #N/A).
> > Can you please tell me where the data should be. Also, the amounts I
> > am looking up are not exact. Does this matter?
> >
> > Thanks again.
> >
> >
> > "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> > news:uaTICBzfEHA.2848@TK2MSFTNGP10.phx.gbl...
> >> Hi
> >> try the following formula in C2:
> >>
> =INDEX(OFFSET('sheet1'!$A$2:$A$10,0,MATCH(A2,'sheet1'!$A$1:$C$1,0)),MAT
> >>
> CH(B2,OFFSET('sheet1'!$A$2:$A$10,0,MATCH(A2,'sheet1'!$A$1:$C$1,0)),-1))
> >>
> >>
> >> --
> >> Regards
> >> Frank Kabel
> >> Frankfurt, Germany
> >>
> >>
> >> bg wrote:
> >>> In Excel2000, I need help with the following formula:
> >>>
> >>> my data is arranged like this ( I can rearrange if needed)..
> >>>
> >>> joe    pete  sue   rating
> >>> 30      10     5        A
> >>> 20       5      3        B
> >>> 10       2      1        C
> >>>
> >>> I then have values for joe, pete, sue. I want to look up the name
> >>> and then give me the rating. The numbers may not be exact. For
> >>> example, I want the output to look like this ( with the formula
> >>> looking up the rating)
> >>>
> >>> name amount  rating (formula needed)
> >>> joe       11         C
> >>> pete       7          B
> >>> sue         1          C
> >>>
> >>> Can someone please help me with the formula to find the rating.
> >>>
> >>> Thanks
>


0
sugah (4)
8/11/2004 10:40:37 AM
Reply:

Similar Artilces:

Pivot Table Splits Columns for same entry #2
Thanks for the response. However, I seem to have misrepresented m problem because the solution doesn't help. In the Layout for the Pivo Table I have the following: In the "ROW": Item#, In the "COLUMN": Tota Lost $, Frequency of Reports generated by item #, In the "DATA": sum th counts of each item #. My chart displays the Item# on the x-axis, th sum the counts of each item # on the y-axis, the Total Lost $ and th Frequency of Reports generated by item # in the legend. There should only be one column per item # whether the chart type i stacked or clustered. I...

Can you automatically date stamp emails that you send out? #2
My emails need a date stamp on them as I send them. It does not appear that you can do this..does someone have an idea to do this? Thanks ...

upgrade problems from 1.0 to 1.2
After upgrading from 1.0 to 1.2 CRM I now received the following error, see bottom of message. The upgrade seem to run smoothly - no visible errors. I noticed that the framework still shows 1.0 shouldn't it be 1.1? Server Error in '/' Application. ----------------------------------------------------------- --------------------- Configuration Error Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately. Parser Error Me...

lotro gold | lord of the ring online | wow | buy /sell #2
http://www.wowmmocom.com you can buy the cheapest lotro gold in our website http://www.wowmmocom.com,we'll provid you the reliable and instant delivery our msn: julieook@hotmail.com website: http://www.wowmmocom.com ...

ANN: HTTPMP
HTTPMP is MAPI transport service provider used to access HTTP mail service accounts (commonly Hotmail). HTTPMP could be used with MAPI compatible client applications (such as Microsoft Office Outlook) to send/receive Hotmail messages in much the same way as POP3 transport. Supported features 1. Sending/receiving messages 2. MIME encoded messages 3. HTML, RTF and text formatted message body 4. Messages with attachments 5. LAN and dial-up mail service account access and polling 6. Auto dial and auto hang-up 7. Fast asynchronous Internet operations 8. Internet connection configuration page HTTP...

OWA and Multiple Domains #2
We just setup a spin off company as another tree in our AD forrest. Users can get into our Exchange servers from their desktop via Outlook but not via OWA. When you go either directly through the Exch server or via our front-end it fails. I am assuming it has something to do with Exch/OWA not knowing how to auth to this new domain. Thanks Again!! In IE 6&7 we need to prepend our username with our domain\. domain\username password Have you tried that? "jwilmer" <jwilmer@discussions.microsoft.com> wrote in message news:520F2FCA-6D3C-4FEB-B6B4-9337E1BF785D@micro...

Determining the apex of a order 2 polynomial
History: I created a spreadsheet with functions only that will provide feedback from a data point on an xy scatter chart. I charted several thousand cells with simple functions such that =if(and(a,b),1,0) to provide feedback based on these coordinates and then used v or h lookup to come up with the adjusted information. This is for a field determination of what the data points should produce. I thought it would be nice to duplicate the form for a similar process in the lab. We frequently test the field information to gain more exact information in the lab. This would allow me to show...

Please Help #2
I am trying to share out a users calander to a group of managers. When I go to properties on the calander it adds them ok. When I go to add them in the properties under the users mailbox in Outlook 2002 on Windows NT it gives me the message "The modified permission could not be saved. The client operation failed." I have read article 323611 on a workaround, but this did not work. We are using Exchange 2000, and I know when I started my predisesor was backing up the M drive on the Exchange server, so when I look at the security settings on the users box on the M drive it...

Question about Pictures in Excel #2
Maybe I should explain more in detail...here is what I have... Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cance As Boolean) Dim testStr As String Dim myFileName As String Dim myPict As Picture Set Target = Target(1) myFileName = "C:\Documents and Settings\ahoekst\My Documents\DirectX _ & "\My Pictures\" _ & Me.Cells(Target.Row, "D").Value _ & ".jpg" testStr = "" On Error Resume Next testStr = Dir(myFileName) On Error GoTo 0 If testStr = "" Then MsgBox "file not found" Exit Su...

How do I create a one variable data table? #2
...

Y scale for P. ie when P$= 2, QD=10 & QS=3 P needs to be Y scale
I am trying to create a graph the compares Quantity Demanded and Supplied in relation to a particular price to find the equilibrium (intersection). The problem is, I have no idea how to make one row the Y scale in my graph. and the other two (QD and QS) the two line graphs which should intersect. For example at a price $0, the QD is 11 units and the QS is 0. At a price $ 4 the QD is 9 units and the QS is 3 units. So as price increases/decreases, demand and supply changes! Hi, Maybe Tushar Mehta's page can help you out. http://tushar-mehta.com/excel/charts/supply_and_demand/ Cheers ...

Double Click on file and mouse not working
Good afternoon, thanks for taking time to look at my problem. I am trying to open a word document by double clicking on the file name in "my documents". Word opens, but not the document. If you go into File and Open, you can open the document, but the mouse cursor will not go anywhere in the document. I have tried to open Word in safe mode, blasted the normal.dot, copied a normal.dot from another computer, heck I tried disassociating doc from Word. Nothing worked. I even installed Windows Live so I could uninstall it. I'm hoping someone has fixed this problem. ...

Lookup/Find help
Windows XP Professional Office 2000 Hypothetical, but hopefully you'll get the gist of it: I have two worksheets. On worksheet #1, I have two columns. First column is a list of entire workgroup by name and 2nd column is the hours worked. On the second worksheet I simply have an list of names that is a subgroup of those on the first page. These indicate a target group. EXAMPLE Worksheet #1 Sam 35 Joe 37 Mary 20 Beth 41 Ted 38 Worksheet #2 Joe Beth Now, on the first worksheet, I want to add a third column for summing only the target workgroup. Basically, I need a function...

Formatting Issue #2
I have a cell range that have Last Name, First Name format. The problem is that some cells have a space between the comma and the first name and some have no space. This is causing a problem when I try to use the =substitute(A1,",",", ") formula because it adds an extra space in the ones that already have a space. Any ideas on how I can keep the ones that already have a space from getting an extra space? Thanks for the help. Hi try: =substitute(substitu�te(A1,", ",","),",",", ") -- Regards Frank Kabel Frankfurt, Germany ...

message rule #2
I set a message rule that when I receive an e-mail with an attachment, send an automatic reply message, this message is an HTM file with a background and a small.gif file, why the sender just recive the text of the HTM file but is missing the background and the .gif? thanks Mario. ...

Disappearing Emails #2
This is a classic but I really neded help as I am new to E2K3. I completed the installation of Exchange 2003 and joined an existing Exchange 5.5 site. I installed an Intenet SMTP connector on E2K3 not realising that it woud also update the existing 5.5 environment. This would the mean that emails sen from the site would reroute through the E2K3 server. There was a report that emails have not been flowing for sometime. After investigating and realsing that the installed connector to the new E2K3 server was reourting all these emails. I looked on the queues in the E2K3 server and eac...

excel CountIF function #2
Hello, I have 2 columns in which first column is Sales name and another is date. I want to know the number of times, did the salemans visited for a particular date. I tried with countif function and cretiria used be and condition, since there is two conditions which has to fulfill. Please mail me if someone knows it? =SUMPRODUCT(--(A1:A100="Bill),--(B1:B100=--"2004/08/12")) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Rajashree" <rajashree@mangaloreuniversity.ac.in&...

XmlSchema.Read not creating the SOM #2
I'm using the following code to create an XmlSchema from an XSD file The Read() method doesn't throw any errors and after I write the schem to a file, it looks as expected. However, after the Read() method is called, m_Schema still contains n items. What am I doing wrong? My .xsd file validates fine in XMLSpy, but it i quite complex, so maybe .Net can't handle it? // Code follows System.Xml.XmlTextReader m_XMLReader = ne System.Xml.XmlTextReader(m_SchemaLocation); System.Xml.Schema.XmlSchema m_Schema System.Xml.Schema.XmlSchema.Read(m_XMLReader, null); System.Xml.XmlTextWrit...

email takes more than 2 hoursto reach the other end!!!!
outlook 2010. The email I sent with it takes more than 2 hours to reach the other end. I have it sync with my hotmail account, calendars work great... I had this problem also with office 2007 and I hoped with the upgrade will be resolved, I tried reinstalling it but didnt work.... any ideas? Is it taking 2 hours for the mail item to move out the Outbox in Outlook? Has this happened when you login to Hotmail and send the message via the Hotmail website? "EvitaOutlook" <EvitaOutlook@discussions.microsoft.com> wrote in message news:04F66290-DEE1-4DAD-9246-25F283BB...

blank two fold 8 1/2 x 14 brochure templates
where can I find a download for a tw 0r three fold blank 8 1/2 x 14 brochure template It is a simple page setup. Setup your printer for legal stock and then your page. In the arrange menu select two or three columns. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "kawika" <kawika@discussions.microsoft.com> wrote in message news:E99D8D0A-E6C8-48A2-A5AF-C5B6E365219C@microsoft.com... > where can I find a download for a tw 0r three fold blank 8 1/2 x 14 brochure > template ...

changing lookup columns
Anybody know a way to add a column to the LookUp screen for Accounts? When I am in a contact record and want to choose a parent account, I would like more columns to distinguish between the different accounts that come up. This isn't the Advanced Find or Associated View for Accounts so I can't see where to add the columns. I've even checked the QueryBase table and don't see any one that looks like it applies. Thanks. There is no supported way to do this in v.1x. Check this folder if you feel curious: \\CRMSERVER\c$\Inetpub\wwwroot\_controls\lookup\lookupxml "A...

NDR for invalid email address #2
I have a user who accidentally sent a message to a fax number from Outlook. Now he gets a NDR The e-mail system was unable to deliver the message, but did not report a specific reason. Check the address and try again. If it still fails, contact your system administrator. I have looked everywhere to eliminate the message so he will stop getting the NDR but can’t find it. I have Exchange 2003 with an Exchange 5.5 as a connector to the internet. Can someone shed some light on this? Thanks Unless there is something funky going on, Exchange only tries to deliver a message once. ...

Template Wizard #2
Hi, I've created a form template that copies worksheet data to a database using template wizard. When using it, it creates a record in database, but when I want to update an existing record, it does not give me the option to "update existing record", only get options "create new record" & "continue without updating". How do I correct this. Note: database contain multiple sheets. When new record is created, all sheets within database are updated without any problems. Only when I need to update existing record do I come across this problem. Th...

Graph
Hi, Here is the problem - There is a graph I have to create, based on the following information: I have projects names, and each project has the following data: 1. Estimated cost. 2. Actual cost. 3.Status, which can be one of 3: a. over with b. in progress c. per demand I need to put the data on a 3D column graph, in a way that each project will have 2 columns that compare estimated and actual cost, but the problem is I also need to put some kind of an attribute on every project which reflects its status. How do I do that? Convert the status to a number you can display eg 0 = overwith, 1 = ...

Beginner question #2
Please forgive what might be a beginner question. I have a spread sheet that is made up of 10 columns. I would like to make it so that each time I change a number in these columns, the sub-total at the bottom of each column changes automatically AND so that the sub-total in the far right column changes automatically. Is there a template for this?? Thanks so much!!! You would use a function for that, lets say you have values in A1 to A10, then in A11 place this =SUM(A1:A10), or =A1+A2+A3+A4+A5+A6+A7+A8+A9+A10. got the idea?? "jenny" <jenny@hotmail.net> skrev i melding ne...