Vlookup Formula #3

Hi,
column J has dates (days of each month). Column K has numbers. the whole
rang is J7:K38.
E12 has a date and F12 has a number.

In cell G12, I need to:
=F12*K? (that has the corresponding date as in cell E12)

ie. if E12 has date 14/10/07 then look for the same date in range J7:J39 and
take the corresponding number in range K7:K39 and multiply it with the value
in f12 and put the answer in cell G12. All in the same s

Khalil]
Hope to have an answer!!



0
khhandal (39)
11/23/2007 12:43:35 PM
excel 39879 articles. 2 followers. Follow

4 Replies
360 Views

Similar Articles

[PageSpeed] 17

=VLOOKUP(E12,J7:K38,2,FALSE)*F12
if you indent to copy the formula down the column
=VLOOKUP(E12,$E$7:$K$38,2,FALSE)*F12
best wishes
-- 
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Khalil handal" <khhandal@yahoo.com> wrote in message 
news:%234Gsy5cLIHA.3852@TK2MSFTNGP06.phx.gbl...
> Hi,
> column J has dates (days of each month). Column K has numbers. the whole
> rang is J7:K38.
> E12 has a date and F12 has a number.
>
> In cell G12, I need to:
> =F12*K? (that has the corresponding date as in cell E12)
>
> ie. if E12 has date 14/10/07 then look for the same date in range J7:J39 
> and
> take the corresponding number in range K7:K39 and multiply it with the 
> value
> in f12 and put the answer in cell G12. All in the same s
>
> Khalil]
> Hope to have an answer!!
>
>
> 


0
bliengme5824 (3040)
11/23/2007 12:55:50 PM
Does your range end at Row 38 or 39?

Anyway, try this in G12:

=F12*LOOKUP(E12,J7:K38)

*IF* Column J is in chronological order.

If not, try this:

=F12*VLOOKUP(E12,J7:K38,2,0)

Format G12 to General or Number.
-- 
HTH,

RD

----------------------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
----------------------------------------------------------------------------------------

"Khalil handal" <khhandal@yahoo.com> wrote in message 
news:%234Gsy5cLIHA.3852@TK2MSFTNGP06.phx.gbl...
> Hi,
> column J has dates (days of each month). Column K has numbers. the whole
> rang is J7:K38.
> E12 has a date and F12 has a number.
>
> In cell G12, I need to:
> =F12*K? (that has the corresponding date as in cell E12)
>
> ie. if E12 has date 14/10/07 then look for the same date in range J7:J39 
> and
> take the corresponding number in range K7:K39 and multiply it with the 
> value
> in f12 and put the answer in cell G12. All in the same s
>
> Khalil]
> Hope to have an answer!!
>
>
> 


0
ragdyer1 (4060)
11/23/2007 1:32:54 PM
Hi,
used this formula: F12*VLOOKUP(E12,$J$7:$K$38,2,0)
it worked well even when copying down the colomn
Thanks

"RAGdyer" <RAGdyer@CUTOUTmsn.com> wrote in message 
news:e09YaVdLIHA.1212@TK2MSFTNGP05.phx.gbl...
> Does your range end at Row 38 or 39?
>
> Anyway, try this in G12:
>
> =F12*LOOKUP(E12,J7:K38)
>
> *IF* Column J is in chronological order.
>
> If not, try this:
>
> =F12*VLOOKUP(E12,J7:K38,2,0)
>
> Format G12 to General or Number.
> -- 
> HTH,
>
> RD
>
> ----------------------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ----------------------------------------------------------------------------------------
>
> "Khalil handal" <khhandal@yahoo.com> wrote in message 
> news:%234Gsy5cLIHA.3852@TK2MSFTNGP06.phx.gbl...
>> Hi,
>> column J has dates (days of each month). Column K has numbers. the whole
>> rang is J7:K38.
>> E12 has a date and F12 has a number.
>>
>> In cell G12, I need to:
>> =F12*K? (that has the corresponding date as in cell E12)
>>
>> ie. if E12 has date 14/10/07 then look for the same date in range J7:J39 
>> and
>> take the corresponding number in range K7:K39 and multiply it with the 
>> value
>> in f12 and put the answer in cell G12. All in the same s
>>
>> Khalil]
>> Hope to have an answer!!
>>
>>
>>
>
> 


0
HANDALK (143)
11/24/2007 7:20:51 PM
You're welcome, and thanks for the feed-back.
-- 
HTH,

RD

----------------------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
----------------------------------------------------------------------------------------

"Khalil Handal" <HANDALK@stthom.edu> wrote in message 
news:O2UPj8sLIHA.4808@TK2MSFTNGP05.phx.gbl...
> Hi,
> used this formula: F12*VLOOKUP(E12,$J$7:$K$38,2,0)
> it worked well even when copying down the colomn
> Thanks
>
> "RAGdyer" <RAGdyer@CUTOUTmsn.com> wrote in message 
> news:e09YaVdLIHA.1212@TK2MSFTNGP05.phx.gbl...
>> Does your range end at Row 38 or 39?
>>
>> Anyway, try this in G12:
>>
>> =F12*LOOKUP(E12,J7:K38)
>>
>> *IF* Column J is in chronological order.
>>
>> If not, try this:
>>
>> =F12*VLOOKUP(E12,J7:K38,2,0)
>>
>> Format G12 to General or Number.
>> -- 
>> HTH,
>>
>> RD
>>
>> ----------------------------------------------------------------------------------------
>> Please keep all correspondence within the NewsGroup, so all may benefit !
>> ----------------------------------------------------------------------------------------
>>
>> "Khalil handal" <khhandal@yahoo.com> wrote in message 
>> news:%234Gsy5cLIHA.3852@TK2MSFTNGP06.phx.gbl...
>>> Hi,
>>> column J has dates (days of each month). Column K has numbers. the whole
>>> rang is J7:K38.
>>> E12 has a date and F12 has a number.
>>>
>>> In cell G12, I need to:
>>> =F12*K? (that has the corresponding date as in cell E12)
>>>
>>> ie. if E12 has date 14/10/07 then look for the same date in range J7:J39 
>>> and
>>> take the corresponding number in range K7:K39 and multiply it with the 
>>> value
>>> in f12 and put the answer in cell G12. All in the same s
>>>
>>> Khalil]
>>> Hope to have an answer!!
>>>
>>>
>>>
>>
>>
>
> 


0
ragdyer1 (4060)
11/25/2007 3:46:00 PM
Reply:

Similar Artilces:

Limiting formulas based on filtering?
Is there an easy way to build a formula that will only take values into consideration when the auto format is used. So, if I have a table of data and a formula based on this table and I filter one of my data columns to some vause then I would like my formula to automatically update. Is this possible? Thank you, CH The SUBTOTAL function ignores rows that are hidden by an autofilter (and optionally, also rows that were hidden manually). It can perform a wide range of operations, including sums, averages, counts, standard deviations, products, maximums, minimums, etc. Check E...

Question #3
Hi I have some data that I graph. Column A has a date [has data in 12 rows] Column B has a data entry (number) [has data in 1st 6 rows] Column C has a data entry (number) [has data in 1 st 6 rows] Column D has a formula that calculates a % on column B & C [formulas in 12 rows] The graph range I have covers the whole 12 rows but it seems to read the formulas in col D and plots every row Is there something I can add to my formula that stops the graph from plotting it unless there has been an answer returned in it? Formula I have is : =IF(B5+C5<1,"",(B5-C5)/B5) Thanks ...

Defrag question #3
I have a windows 2000 server that's a file/print server and Exchange 2000 also installed and running on the same box. I was wondering what is the best way if I want to run defrag and scandisk on the disk drive? I guess I'm asking because exchange is running on the box and I don't know if I run defrag and scandisk will I run into problems or cause the database problems. Thanks for your help. John In article <4D7A888F-B293-400C-A77C-DC379DDF4BDF@microsoft.com>, John@discussions.microsoft.com says... > I have a windows 2000 server that's a file/print server and Ex...

Pivot Table Help #3
I have a lot of data that I am trying to analyze with a pivot table and am not sure how to go about it. Columns are (1) District (2) Store # (3) 2003 Score - these are #s or text ("incomplete") (4) 2004 Score - these are #s or text ("incomplete") For each district, I am trying to find out 3 things: (1) % of stores incomplete (2) Average score for 2003 & 2004 - I've got this one working properly (3) % change between 2003 & 2004 I can successfully analyze the data in a spreadsheet but there is too much to go through and thought a pivot table was the way to ...

Emailing from Publisher #3
A friend has OFFICE 2003. Outlook and Publisher are both 2003. BEFORE I cleaned up temp files, she was able to "Send Page As Message" in Publisher. Now, she gets "Publisher cannot mail your publication because part of the mail system is missing..." All I did was delete temp files ("C:\documents and settings\[username]\local settings\temp" and "C:\windows\temp"), and run "ccleaner". I've put off the "reinstall your mail program" suggestion from the error message as I don't know if she can find the install CD... Is there any ...

item look up #3
a customer is having an issue. he scans an item in the pos screen and all details show up. but when he tries to look for it later he cant find it. hes tried it in manager too. he would usse name, or barcode, or anything and still nothing. do we know what the problem is? Thanks If the item is set as inactive (in the item properties) it will not show up in the Find screen, but will scan in at the POS. You can turn off the find option, but I think it is easier to use one of the SOM reports to find the item, such as the price report. The item will show up in any report that includes it as...

eft for payables #3
Can I use a current vendor and make them an eft vendor? I tried testing this and it did not work. I went back into my test company and the reason the current vendor was not working was because I did not generate a pre-note first. "LauraS" wrote: > Can I use a current vendor and make them an eft vendor? I tried testing this > and it did not work. You should be able to do this as long as you use the EFT for Payables Vendor set up to set up the banking information for the vendor. Setup -> Purchasing -> EFT Payables Setup -> EFT Vendor Cards "LauraS" ...

grid_container.aspx equivalent in 3.0
I was wondering if anyone knew of the 3.0 equivalent of grid_container.aspx in MSCRM 3.0. In 1.2 it was so easy just to link to that page, and pass in the view id to display a view from a third-party app (http://localhost/_grid/grid_container.aspx?viewId={DCF11D6A-4EAB-40C8-801A-0132C5FF092F}). It appears there is not the same concept in 3.0. Can anyone shed any light on this for me? Thanks They (aka me :-) ) rewrote the grids in CRM 3.0 and they no longer use IFRAMEs. Instead the first page load just renders the HTML inline into a DIV. For subsequent loads (like paging, sorting and ...

Microsoft CRM 3.0 (KB922815) Installation Error
When i try to install Update Rollup 1 for MSCRM 3.0 i get following error. Setup could not find Microsoft CRM 3.0 Server or you have a mismatch between update language and Microsoft CRM language. The installation of this update has stopped. Is this rollup language dependent? I have a swedish installation of MSCRM. Thanks! // Mats ------=_NextPart_0001_11F19277 Content-Type: text/plain Content-Transfer-Encoding: 7bit Yes this rollup is language dependent. You need to install SVE(swedish - sweden) UR1 package. Thanks, Indira Dhingra This posting is provided "AS IS" with no...

CRM 3.0 internal recipients don't recieve mails
Hi everybody... We've installed CRM 3.0, including the Exchange Route Component on an Exchange Server 2003. Inbound and outbound mails, mail tracking and so on are working correctly, but if someone sends an email using the CRM WebInterface or the CRM Outlook Client and uses an email-recipient of our internal domain (e.g. as TO, CC or BCC with adress username@mydomain.com) the mail is not send. An activity that describes that email ist correctly generated by the CRM, but the mail itself is not send to any of the listed recipients, if at least one of them belongs to our domain. Doe...

Formula in Marksheet #2
Hi all... I need help on a 'formula' for my students' marksheet. For them to pass the whole exam, a student MUST PASS 7 SUBJECTS from 3 groups. The grouping is like these : A) Must pass ALL 4 subjects in Group A - marks are in cells D7, F7, H7, J7 B) Must pass AT LEAST 2 OUT OF 3 subjects in Group B - marks are in cells L7, N7, P7 C) Must pass AT LEAST 1 OUT OF 2 subjects in Group C - marks are in cells R7, T7 Thank you so much something like? if count(d7,f7,h7,j7)<4,"bad","good") -- Don Guillett SalesAid Software dguillett1@austin.rr.co...

Quote for Customer can't find data source #3
I need assistance finding the data source for the quote mail merge in CRM 4.0. I get a error "mail_merge_64494[1].doc is a mail merge document. Word cannot open the data source, ." Here is the process I go through before I get the error: - I open a quote - click on menu bar button "Print Quote for Customer" - select organization mail merge, Quote for customer, and OK - when asked if I want to open or save, I select open - I pick yes, when it asks if it can put data on my document I then get message above. How do I find the datasource? Also is there a way to configure...

formula for money additions & subtractions
I am trying to get a formula to calculate payroll deduction for cas register +/-. When an employee is over by $10 or under by $24, th difference comes out of thier check. Example: if somone is -25.00 for the month $1.00 would come out of th check. If someone is +11.00 then $1.00 would come out of the chec also. the +/- for the month is in one cell so i would like the formula to b able to read that cell and figure out if it is too much or little fo the deduction. this is the greatest sight that i have ever found and i hope someon can help me Thank you, Mik -- Message posted from http...

What is the formula for series: Jan-04, Feb-04, Mar-04, etc.?
Hi Kitty, =DATE(YEAR(A1),MONTH(A1)+1,1) -- Kind Regards, Niek Otten Microsoft MVP - Excel "kitty" <kitty@discussions.microsoft.com> wrote in message news:2C586891-2883-47B9-A3DE-AF77D4A9799E@microsoft.com... > Hi kitty, One way of doing it is with the formula below: If you have a date is cell A1 (01/01/04) and you want cell B1 to sho you the following month insert in B1 the formula below. =EOMONTH(A1,0)+1 You may have to install the "add in" the ToolPak in order for th formula to work other wise you will get a #NAME?, error. Hope thi helps -- Fa...

Internet Mail #3
Hey all, I've got an ISP servicing our Internet mail, but I use Exchange 5.5 internally, one of the users is sending mail out to the Internet via the Exchange account but I want it to use the ISP service, does anyone know how to default to the ISP Internet Mail. Thanks in advance, Craig Craig <anonymous@discussions.microsoft.com> wrote: > I've got an ISP servicing our Internet mail, but I use > Exchange 5.5 internally, one of the users is sending mail > out to the Internet via the Exchange account but I want > it to use the ISP service, does anyone know how t...

countif? #3
hi, i'm stuck with a formula. I need to count the number of cells in the whole of column K tha contain the numbers 6,7 and 8. I can't seem to find a way to do this, Any help would be greatfully recieved Regards Pet -- Message posted from http://www.ExcelForum.com try this. You cannot use A:A here. =SUMPRODUCT((A2:A10={6,7,8})*1) -- Don Guillett SalesAid Software donaldb@281.com "Pete JM >" <<Pete.JM.16713e@excelforum-nospam.com> wrote in message news:Pete.JM.16713e@excelforum-nospam.com... > hi, i'm stuck with a formula. > > I need to count...

time formulas
Can anybody give me an advice Lets say i put in first column dime of departure and in second one time of arrival (of course i know have to insert the time ). In third column i get the time spent somwhere. So far everything is ok. The problem occ urs when i want the sum of all differences in the third column (rows are months dates). The autosumm formula wont work and the result is completely wrong. thanks miro Hi format the resulting cell with the custome format [hh]:mm -- Regards Frank Kabel Frankfurt, Germany axiom wrote: > Can anybody give me an advice > > Lets say i put i...

xy Scatter chart #3
I will try to explain this to the best of my knowledge: I have a spreadsheet with some information that is set up like this: Example Risk Impact Probability Director Trls Med 3 1 Wrong Trips High 4 3 Upgrade JAX Low 1 1 These numbers are generated from a series of questions that determine wether the risk is a high, med, or low factor. So there is really no way of knowing what numbers determines a Low, Med, or High Risk The numbers will never go higher than a 4 and lower th...

C#, .net 3.5sp1 and Visual Studio 2008 sp1 errors list
What kind of errors will the errors list show when using C# 3.5sp1 with Visual Studio 2008sp1? "Andy B." <a_borka@sbcglobal.net> wrote in message news:%23tmIzu4vKHA.404@TK2MSFTNGP02.phx.gbl... > What kind of errors will the errors list show when using C# 3.5sp1 with > Visual Studio 2008sp1? e Why would you expect any? On the face of it vs 2008 sp1 will target .Net framework 3.5 sp1 by default. Andy B. wrote: > What kind of errors will the errors list show when using C# 3.5sp1 with > Visual Studio 2008sp1? That all depends on what ki...

Cannot view any form in crm 3.0
We have just installed CRM 3.0 on MS server 2003. I have created an account which has a system administrator role assigned to it. The issue is that when I open CRM web (on the server) when I select account, contact, activities, calendar etc... I get the following error: An error has occured. For more information, contact your system administrator. Now if I open CRM web using an account which has restricted access mode ticked, I don't get any errors, everything opens fine. The problem is that with an restricted access code i can't do much. I have tried assigning different roles ...

CHtmlView question #3
Is there an easy way to get the source of the HTML document? On MSDN online, there's a GetSource(CString& str) listed but that's apparently not implemented in VC++ 6.0. ...

Formula for excel example 555 x 15%
=555*0.15 -- 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 "sari" <sari@discussions.microsoft.com> wrote in message news:7B4BA8D5-04A4-4195-B749-DFEED0452A3A@microsoft.com... > ...

Pasting Formulas bug?
I seem to be having a strange problem with my Excel (11.2.5) for Mac. I have pasted formulas for years and never had an issue...until now. The formulas are simple (addition, subtraction, etc.). I copy the formula in a cell and paste it to a new one (I even do this with Paste Special...Formula). What appears in the spreadsheet is the value of the original cell, not the result of the "relative" formula -- even though the formula bar displays the correct "relative" formula. I can only get the correct value in the new cell if I go into the formula bar and press return at the...

CRM 3.0 SDK Web Services
Hi I am writing a small data migration tool to migrate account and contact data. I have approximate 1500 accounts and 6000 contacts to import to CRM 3.0. I finished the application and ran the data migration, I will able to migrate all of the account records without any problem at all. when I run the contact migration, it can migrate 2800 contact records when it get over 2800, my application throw me the following error message: The underlying connection was closed: Unable to connect to the remote server. I am running CRM 3.0, SQL server and my data migration on the same server. I am wonder w...

Pivot table
Can I develop a formula that I can add to those which you pick from whe using the wizard ie sum, average, min, max etc Specifically, I want to add an IF statement to give me a 'flag' i which to summarize the data with elsewhere. The data behind the pivo changes (sales data) and I am trying to flag new customers that hav never worked with us before.....once they have traded with us then the dissappear as they are now an old customer To -- Message posted from http://www.ExcelForum.com Hi no you can't do this -- Regards Frank Kabel Frankfurt, Germany > Can I develop a for...