#### Lookups #2

I am trying to write a formula which looks up a product code in a list,
and then references a date to pick out a piece of information.

The code is in a horizontal list with the dates being in a vertical
list across the top of the page.

eg
Code      Dates
Jan         Feb        Mar
111         50           70         80
112          90           10         60
113          60           10          80

It needs to refer to the code then the month, to be able to select the
correct cell.

eg
code 112 in February = 10

Is there a function to cross reference the two pieces of information to
select the data.

Any ideas would be great

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

 0
11/7/2003 1:48:45 PM
excel.misc 78881 articles. 5 followers.

2 Replies
697 Views

Similar Articles

[PageSpeed] 26

"patrickbirch" <patrickbirch.wj0tb@excelforum-nospam.com> wrote in message
news:patrickbirch.wj0tb@excelforum-nospam.com...
>
> I am trying to write a formula which looks up a product code in a list,
> and then references a date to pick out a piece of information.
>
> The code is in a horizontal list with the dates being in a vertical
> list across the top of the page.
>
> eg
> Code      Dates
> Jan         Feb        Mar
> 111         50           70         80
> 112          90           10         60
> 113          60           10          80
>
> It needs to refer to the code then the month, to be able to select the
> correct cell.
>
> eg
> code 112 in February = 10
>
> Is there a function to cross reference the two pieces of information to
> select the data.
>
> Any ideas would be great

Your post makes me very confused, I'm afraid! You talk of code being in a
horizontal list and then show it vertically. Then you say: "dates being in a
vertical list across the top of the page".

If it helps, this formula looks up A1(row) and B1 (column) in an array named
'Table':
=INDEX(Table,MATCH(A1,INDEX(Table,,1),0),MATCH(B1,INDEX(Table,1,),0))

 0
Paul
11/7/2003 1:59:56 PM
If you have named ranges, you can simply use =Code*Month, OR in this case
=112*Feb or simply =112single spaceFeb

--
Greeting from the Gulf Coast!
http://myweb.cableone.net/twodays
"patrickbirch" <patrickbirch.wj0tb@excelforum-nospam.com> wrote in message
news:patrickbirch.wj0tb@excelforum-nospam.com...
>
> I am trying to write a formula which looks up a product code in a list,
> and then references a date to pick out a piece of information.
>
> The code is in a horizontal list with the dates being in a vertical
> list across the top of the page.
>
> eg
> Code      Dates
> Jan         Feb        Mar
> 111         50           70         80
> 112          90           10         60
> 113          60           10          80
>
> It needs to refer to the code then the month, to be able to select the
> correct cell.
>
> eg
> code 112 in February = 10
>
> Is there a function to cross reference the two pieces of information to
> select the data.
>
> Any ideas would be great
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>

 0
twodays (259)
11/7/2003 2:01:30 PM

Similar Artilces:

Reports #2
Hi, does RMS has a report, that will show every Item sold, layaway, work order. for specific day? Thanks -- Reynold Cycle Unsure myself if there is anything more specific than my suggestion, but as for the items sold use the "Item Movement" report and filter on todays date & Sales >0. You can also filter on the date on the Layaway & Work Order reports. Hope this is what you were looking for. -- Jeremy Janisch "Jose" wrote: > Hi, does RMS has a report, that will show every Item sold, layaway, work > order. for specific day? > Thanks > >...

Outlook synchronization error #2
Good evening, we are using Outlook 2003 and MS exchange 2003 with all the patches and updates. We are having this reoccurring problem with a number of users. So far we have tried the following: rebuilt the profiles, the database as well as the OST with no luck. The message mentioned under "Synchronizing local changes in folder 'Conflicts' appears actually in the inbox! The link provided with the error takes you to the Microsoft support site and there is no solution there. We will be very grateful if anyone has any suggestions on how to fix this. Here is the error: 20:14:15 ...

change button colour #2
that works just as well although it takes a bit of work to get spot on thank -- jimbo69 ----------------------------------------------------------------------- jimbo693's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1421 View this thread: http://www.excelforum.com/showthread.php?threadid=26732 ...

stacked bar charts #2
I'm trying to compare timeliness performance across several different courts. I have data showing me how many cases are completed in each court in intervals such as 0- 35 days, 36-71 days... I want to be able to stack this info vertically for each court, giving a total of 100% of cases completed for each court. Then I want to be able to compare the courts horizontally so I can see, for example, which courts are clearing large %'s of very old cases, or very young cases. Suggestions welcome. ...

Convert number into words #2
Hi, Can any one help me to convert numeric into words (without Currency USD, EURO, just words), e. g., 15,015.27 Fifteen thousand fifteen and twenty seven only Rgds Blackwar Blackwar, have a look here for how to do it, http://www.xldynamic.com/source/xld.xlFAQ0004.html -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Blackwar" <Blackwar@discussions.microsoft.com> wrote in message news:C81ED27F-49A4-40A6-91E5-5ED1A9...

Has anybody come across a situation where the Smartlist>>Purchase Orders>>Received Purchase Orders would not show a PO that had a valid Receiving against it without an Invoice. It is a Blanket PO and was received against the right line on the PO - however both open lines of the same item on the PO show as Released and the Smartlist only shows Received status PO's. The odder thing is it does show up on the PO Analysis>>Received/Not Invoiced report. Anybody come across this before. Thanks Was the PO manually closed? That would be a reason the receipt doesn't ap...

Chart automation #2
I have a spredsheet that keeps track of a number of sales items for every month. These sales numbers are also used for the sources in a chart that will give me a visual picture of what is going on. So, if the spreadsheet has numbers in A1 to C3, the source will be A1:C3 Each month I will add a new row to the spreadsheet for the new set of numbers. Then in the chart I will have to manually change the source to A1:C4 and so on for each new month. Is there a way to automatically have the chart to know that there is a new row in the spreadsheet and automaically add that new row to the so...

IMAP4SVC does not start #2
I have MS Exchange 2003 on Windows Server 2003. After last power outage IMAP4SVC does not work. Microsoft IMAP4 service sown as STARTED in services, but Virtual IMAP4 server in System Manager is down. When I'm trying to start it I see the message, that IMAP server shows specific error and can not be started. There is a Warning in Event Log with Source: IMAP4SVC and Event ID: 1036 "An error occured while starting the Microsoft Exchange IMAP4 Service: server instance number 1 failed to start with error 0x36b7" Please advise, thank you, Gary Uber ...

Move PF From 2000 to 2003 #2
We are running EX2000 SP3 and just built an EX2003 SP2 server for migrating all public folders to. In the past (5.5 to 2000), we setup replication for all folders and then remove the old one after they in-sync. This process take very long time. Now, we heard there is a new tool on EX2003 SP2 "Move all Replicas", is that something to replace the old process? How does it work on large data? Will it verify the folders before removing the old data? Our PF currently 160GB in size and we use EFORMS and Event agent a lot. Does the "Move All Replicas" also handle the exchange syste...

We know it sounds TOO good to BE true, but it's REAL! Our International Technology firm will pay you hard cash each time FREE software is downloaded. Best of all you will only have to download ONE piece of software ONE time for FREE and you can be on your way to earning BIG! FIND OUT MORE BY VISITING http://Brennus.PlugUsIn4Cash.com/ ...

How do I sort several 4 digit numbers by the last 2 digits in exc.
I want to sort several 4 digit numbers by the last 2 digits in excel. Excel defaults to sorting by the first 2 digits and I would like to change this I'd use a helper column of cells and sort by that: =mod(a1,100) And drag down. rjbind wrote: > > I want to sort several 4 digit numbers by the last 2 digits in excel. Excel > defaults to sorting by the first 2 digits and I would like to change this -- Dave Peterson ec35720@msn.com i do not know...you could insert a column of =right(a1,2) (copied fo the extent of your rows) and do the sort based on that - asuming your di...

Replacing our Server running W2K AS/Exchange 5.5 with our 2 new servers running W2K3S.
Our current network has one of the server running Windows 2000 Advance Server with Active Directory and Exchange Server 5.5 installed on it. We are about to replaced them with two servers, one running Windows Server 2003 with Active Directory and acting as the Domain Controller and the other one also is running W2kS3 with Exchange Server 2003 installed on it. These 2 new servers are running on a private network and our plan is to unplug the current server (W2K AS with Exchange Server 5.5) and replace them with the new 2 servers. We have tested the 2 servers and are working fine, but our conce...

MSCRM 1.2 Installation & Configuration Certification
Hi I've just had a minor coronary. I went on the official 3 day course for the above and have also just completed the MS eLearning course on it. Also installed and used the App extensively. I am taking the exam on Friday and was feeling quietly confident until I just got sight of the Exam Prep guide that says that 14.3% of the questions are on the Data Migration Framework. This is not covered in either of the aforementioned courses (which are the MS recommended ones). I just took a peek at the DM guide, hence the heart problem encountered. Is there any training for this?...I...

exceptional demand #2
There is a check box on the Sales Line item Detail screen titled 'exceptional demand'. The help screen states: If you use ordered quantities to calculate sales forecasts, mark this option if the line item constitutes demand that is more than what is typical for an item-site. This type of demand will be treated differently by a forecasting system. I tested this and MRP does not handle the exceptional demand any differently that regular demand. Does anyone know what the 'exceptional demand' indicator does? Thanks in advance. mickey Exceptional demand, and for that matter the ...

EXCEL caused an invalid page fault #2
HI I HAVE A PROBLEM OF OPENING EXCEL FILE FROM NT SAVER (LAN), THE FOLOWING ERROR MASSAGE APEARS, THIS PROGRAM HAS PERFOMED AN ILLIGAL OPERATION AND THE WINDOW WILL SHUT DOWN. BUT OTHER EXCEL FILES FROM THE SAME SAVER ARE OPENING WITHOUT A PROBLE THIS IS THE PROBLEM DETAI EXCEL caused an invalid page fault i module EXCEL.EXE at 017f:302b8dbf Registers EAX=00000000 CS=017f EIP=302b8dbf EFLGS=0001024 EBX=00000000 SS=0187 ESP=0062cc08 EBP=0062cc7 ECX=0068dc28 DS=0187 ESI=00000011 FS=4cc EDX=00000001 ES=0187 EDI=00000000 GS=000 Bytes at CS:EIP 8b 40 30 3b c7 0f 85 e8 4d e5 ff 57 e8 52 0d d9 St...

Deleted investments #2
Every time I try to use an investment name that I deleted a while ago, I'm told I can't use it again. Is there some way to "empty" the "deleted investments" folder? Thanks! In microsoft.public.money, davenc wrote: >Every time I try to use an investment name that I deleted a while ago, I'm >told I can't use it again. Is there some way to "empty" the "deleted >investments" folder? Thanks! Undelete it by entering a fake dividend for the investment. Then blank out its symbol, and change the name to something you will not wa...

I have create an address list of contacts in the ESM. I have granted full control to the following: Domain Admins Enterprise Admins Exchange Domain Servers SYSTEM As a member of the Domain Admins group, I am able to see the address list as well as list its contents in Outlook. When I log in on a test workstation with a test user, I am still able to see the list but not list its contents in Outlook. The test user is a member of only the Domain Users group. What more must I do to prevent the address list from even showing up in the list of address lists in Outlook for the test use...

Fax #2
How do I set up fax in Outlook 2000? That would depend on which mail support mode and fax software you are using, which you did not provide: http://home.indy.rr.com/russval/fax.htm -- Russ Valentine [MVP-Outlook] "Woody" <Woody@discussions.microsoft.com> wrote in message news:72631958-0A40-4B02-ABF4-CC43D9CE750F@microsoft.com... > How do I set up fax in Outlook 2000? ...

Great Plains and SQL Replication #2
Does anyone know anyone who is using One Way Transactional Replication with Great Plains? We don't need standby, we need Read Only at a different location than where GP is running. Thanks. Contact me at 512.347.9399x109 Also - Here is a White Paper on GP and SQL 2000 Replication. Officially, Replication of any type is not supported and the "resource" who wrote this white paper is no longer available. http://support.microsoft.com/default.aspx?scid=kb;en- us;329185&Product=sql Here is some information from a Microsoft Consultant on One-Way Transactional Replicati...

Security Update for SQL Server 2005 Service Pack 2 (KB970895) 11-23-09
I am unable to download or install this update; Security Update for SQL Server 2005 Service Pack 2 (KB970895). The error message is (733F, unknown error). Tried to reinstall, restarting then tried again, no go. Thanks in advance. Model: HP HDX-HP X16-1044nr 64 Bit System Vista (cross-post added to SQL Setup) "CBear" <CBear@discussions.microsoft.com> wrote in message news:8106DA8D-1AE6-4AB6-8402-7F3C3C4F32DE@microsoft.com... >I am unable to download or install this update; Security Update for SQL > Server 2005 Service Pack 2 (KB970895). > The er...

send email #2
Can Excel automaticly send an email message to an adress contained in a cell when another cell contains the specific text? see www.rondebruin.nl/sendmail.htm -- HTH RP (remove nothere from the email address if mailing direct) "gall" <gall@discussions.microsoft.com> wrote in message news:6224A2FB-B9EF-4599-ACF9-31B485269F17@microsoft.com... > Can Excel automaticly send an email message to an > adress contained in a cell when another cell contains the specific text? > > ...

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

Custom border problem #2
Having difficulty creating a custom border around a text box in Publisher 2000. After seleccting the graphic to use, we get the message that Publisher cannot open the file. It is not a question of file complexity or on a network as we see the same message no matter where the source. The account in question is a limited account, which we are assured, has managed to create custom borders in the past. I tried making a border in the admin account and it works OK which would suggest it is a question of permissions, but why should be so and why did it work once but no longer? TIA TWK Can ...

Assertion error #2
void CJeevanDlg::OnFileOpen() { // TODO: Add your command handler code here int count=0; char buffer[10000]; CString str;str="jeev"; CFileDialog dlg(true,".txt","jeevan"); int x= dlg.DoModal(); str=dlg.GetFileName(); if(!f.Open(str,CFile::modeRead)) CJeevanDlg::OnCancel(); f.Read(buffer,sizeof(buffer)); int p=f.GetLength(); UpdateData(false); buffer[p]=NULL; m_value=buffer[p]; m_ctrl.SetWindowText(buffer); } The error that occurs is that the dialog that is launched using CFileDialog is giving a assertion when i press the cancel button. ...

Version 2 functionality
Is there a published (or best guess) list of enhancements for CRM V2? Not as of yet. Best informtion that is public so far is the high level features in the product roadmap that's been around for awhile. Some of the basics though: - Marketing Campaign support - Service Call scheduling - Revamped security - Revampd Activites (including ability to customize and trigger workflow) - Enhanced workflow - Ability to add new entities & reltaionships between entities - Performance enhancements to SFO - Performance enhancements to the SDK API's Matt Parks MVP - Microsoft CRM ----------...