I have a file with two work sheet, in 1 sheet have monthly allowance to staff in 2nd sheet I need the data in a schedule format. Please see the attached file, my formula not working here properly.
View 6 RepliesI am trying to combine a subtotal formula in sumproduct formula but end in error. Appreciate if someone can assist me with the right formula. I would like to combine subtotal in the following sumproduct formula:
=SUMPRODUCT(((C3:C9)=C14)*((G3:G9)=B15)*(H3:H9))
Looking to nest or combine the following 2 functions because I'm looking up the same cell content, except within 2 different tables.
=IF(B12="","",SUMPRODUCT(--($B$12:$B$17=X18),--($S$12:$S$17))) and =IF(B22="","",SUMPRODUCT(--($B$22:$B$32=X18),--($S$22:$S$32)))
Right now, these functions works well from a single table but need to combine them. Other function examples are welecomed too.
Now I'm trying to improve the functionality of the file attached above.I've attached the file again with another drop-down menu. What I'm trying to do is to have a sum of all the months between the two months indicated in the drop down menu.For example by picking two months from the drop down menus, I want to know the sum of the values between Jan-Mar (Jan, Feb and Mar), or for example Jul-Oct (Jul,Aug,Sep and Oct)...
I've tried to combine SUMPRODUCT with INDEX and MATCH but it doesn't work. I want to build upon this formula:
[Code]....
I forgot to mention that I have multiple strings (column E) which are NOT unique. Basically "VLOOKUP way" won't work, rather it should be "SUMIF way".
In the attached file I've added one additional row (r19), which is the same as r18, just to try how the summing will work.
Dynamic SUMIF(S).xlsx
The attached spreadsheet has a "master" workesheet in which I enter customer info, salesperson info, and date. The totals spreadsheet automatically calculates number of sales, contact value.
I need to modify the following formulas to only calculate the data within a date range shown in 2 cells.
=COUNTIF(Master!A1:A176,PayPeriod!A4)
=SUMPRODUCT(--(Master!A1:A176=Totals!A4),(Master!K1:K176))
=E4SUMPRODUCT(--(Master!A1:A176=Totals!A4),(Master!K1:K176))
I need to lookup two different columns and if values match, then bring back one value:
For Example:
Data:CityNameAtlantaDaveNew YorkCharlieLos AngelesDanSt. LouisDanMemphisSteven
Lookup Table:CityNameCodeAtlantaDaveDAVNew YorkCharlieCHALos AngelesJavierJAVSt. LouisDanDANMemphisRobertROB
The formula I'm looking for would produce the following results:
If City on Data matches the city on Lookup Table, and the name on data matches the name on Lookup Table, then bring back the code on Lookup Table.
Result would be:
Results:CityNameResultsAtlantaDaveDAVNew YorkCharlieCHALos AngelesDanWill be blank, as the names on Data and LookUp Table don't matchSt. LouisDanDANMemphisStevenWill be blank, as the names on Data and LookUp Table don't matcm
Is it possible to combine Vlookup & Max?
In column A I have product codes some of which are repeated, in column B I have costs for the relevant product codes.
I want to vlookup the product code on another sheet, then Columns A & B picking up the cost in column B but if there are mutiple entries I want to pick up the highest cost out of all the entries with that product code.
Let's say for instance I have a vlookup that displays a the quantity of available items in stock pulled from a column in another workbook. Occasionally these numbers are negatives as they are backordered I would like to display these values as zero.
View 2 Replies View RelatedI have an Excel workbook containing a number of worksheets. My problem is that on one sheet(BlkPick) I have Rows containing headings(Location Names)and a column containing Product names. I wish to lookup the corresponding value contained at the matching intersection on another worksheet. I have searched this forum along with google and found that INDEX & MATCH are what I should be combining with Vlookup and these work when used on the same worksheet but when I test using say MATCH alone I get an error when attempting to MATCH my result from a different worksheet.
View 9 Replies View RelatedI am using =max(D:D), & need to return the cell address (using address)
However, then i need to combine this into my vlookup
I have been trying -
=VLOOKUP(ADDRESS(MAX(D:D),4),D:E,2,0)
LEFT(C4,3)
VLOOKUP(C4,C4:D38,2,FALSE)
VLOOKUP(LEFT(C4,3),C4:E38,2,FALSE)
I combined the first two formulas into one (the third one) but it is not working. I get an N/A result.
I need a formula that will combine hlookup and vlookup to return one value.
View 14 Replies View RelatedIs there any way to combine the VLOOKUP and IF Statement formula's into one
formula or is there any other kind of formula which does both?
I'm looking for a formula to return the correct value without using any help-column and it should be typed in no more than one cell.
View 10 Replies View RelatedI think I need a macro to do this. Can I use vlookup function for a range of number? lets say I have number 1001 to 1005. i want to combine the result. any other way other than using this formula
=vlookup(1001,table,2,false)+vlookup(1002,table,2,false)+vlookup(1003,table,2,false)+vlookup(1004,ta ble,2,false)+vlookup(1005,table,2,false)
Is there possibility to define a function that works like say =clookup(numlow,numhigh,table,2,false)?
I have a workbook with two sheets that I need to combine some of the data from. Sheet1 is a template to be used for translation purposes on Sheet2. I would like to put Sales Rep Info in column C on Sheet2 (has only Sales Rep ID) using Sheet1 as a translation table (shows Sales Rep ID and Sales Rep Info). I can do this with a vlookup function but would like to know how to do it in a macro so I can use on other more complex workbooks. Here is a sample data set.
View 3 Replies View RelatedI want to combine both an 'if' and a 'vlookup' within a single cell. So I want to say if the value in cell a1 can be found in columns c1:d100 then return a 'yes' if not return a 'no'. How do I do this
View 7 Replies View RelatedI have a daily tracking sheet. I want (off to the right) to be able to enter start/end dates and have it sum the total grossage for JUST those dates alone. Which function do I use?
http://s401.photobucket.com/albums/pp94/nmweir/?action=view¤t=untitled.jpg" target="_blank">http://i401.photobucket.com/albums/pp94/nmweir/untitled.jpg" border="0" alt="Photobucket">
direct link? :
http://i401.photobucket.com/albums/p...r/untitled.jpg
Column B has the "date"
Column D has the "time"
Column E has the "field" then in
Column F is "VIN" and
Column G is "HOMI"
Then I have another area on my page that I need to redistribute data.
I need a vlookup, sumproduct, or something that will give me the data I want.
here is what i am looking for:
R138 through AE300 is the data area.
Column R has the "date"
Column T has the "time"
Column V and W are "field 1"
Column X and Y are " field 2"
Column Z and AA are "field 3"
Column AB and AC are "field 4"
Column AD and AE are "field 5"
I want to be able to put a formula in the field areas V138:AE300 that will find the "VIN" and put it in my new area and my "HOMI" and put it in the new area.
Is this impossible?
Or is this just wishful thinking?
If I need to give anymore info let me know.
I cannot add any software at work so I can't show you the data I am talking about, unless i can copy and paste the sheet to show you.
this formula works but returns #N/A when the result is false, how can i get rid of N/A but replace with 0.00 even though i have 0.00 as false
=IF(VLOOKUP(A10,Down!$F:$H,3,0)="No",SUMPRODUCT(($A10=Downloads!$F$7:$F$3823)*(CT!E$9=Downloads!$A$7:$A$3823),Down!$D$7:$D$3823),"0.00")
formula below... currently is giving me a:#N/A
=IF(EG55="FCST",VLOOKUP(--EE55,Control!$R$8:$R$20,6,0)*(SUMPRODUCT(--($HH$11:$HH$62=EE55),--($HJ$11:$HJ$62=EJ55),--($HL$11:$HL$62))),NA())
If I remove the vlookup part the formula return the correct results, but I want the resulting number be multiplied by a percentage (that what's the vlookup is trying to accomplish).
What i want to do is to look up of the value of home and away games seperatly. if there is no match for the lookup i get the "N/A" and if there is no value i get "0". If i get 0 the formula will calculate as a lost game.
View 10 Replies View RelatedXL 2003
I want to combine these three functions
=VLOOKUP(e4,AGTNAME!$A$1:$E$3086,5,0)
=LEFT(E4,FIND("/",E4)-1)
and iserror
I have a Vlookup which I want to modify so that it can become dynamic as the table array part of the vlookup will change.
So the basic vlookup is as follows:
=VLOOKUP($R$3,ATTRIBUTION_FACTSET!$M$60:$P$73,2,0)
but the data I am looking for wont always be in the range M60:P73.
So I tried to make it dynamic by doing the following:
=VLOOKUP($R$3,INDIRECT("ATTRIBUTION_FACTSET"&"!M"&U1&":P"&V1),2,FALSE)
The idea being that U1 and V1 would be numbers that can change so in this case U1 would equal 60 and V1 would equal 73
This vlookup is giving me #N/A and no matter how I modify it I cannot get it to work.
I have a sheet using VLookup to find EMail and Web addresses. I can get the address to show up but not as an active URL address. Is it possible to have the address "active" so I can click on it and activate the EMail or Web Site?
View 5 Replies View RelatedI'm trying to do a Vlookup on a file that gets automatically downloaded to the computer from a website. The data is in lots of different data sets, like so:
Loans to countries
Mar
Apr
May
Jun
Loans to banks
Mar
Apr
May
Jun
Every month a new row of data gets added to each table, meaning the start and end cells of the array also shift each time.
I'm currently trying to vlookup the same data from 2 different sheets. Here is the code i've tried.
View 4 Replies View RelatedI’m trying to develop a workbook which holds monthly data on loan information. It tracks the interest and balance on the loan. I want the first page to have a table displaying the interest payments for every individual tab. When I was brainstorming the idea, I was considering a sort of Vlookup function to find the tab the account is on and then a further function, possibly another vlookup which connects the month to that month’s interest payment. Can anyone help me figure this out?
The attached spreadsheet is obviously simplified, there are well over 30 tabs. But I would like it to, ideally, search the account number column, search the workbook for that account number, and then when on that page use the month at the top of the first page and retrieve the interest payment and put it back in the cell. It’d also be great if the formula can be transferred between workbooks. I’m not sure if that makes sense; basically if I were to copy that worksheet into the next months book, I would like that the formula read those tabs instead of becoming obsolete due to references from the first workbook.
I have data in a pivottable which I want to compare with another table. The lastcolumn+1 of the pivottable needs to get data from another table. I have this for an example:
View 5 Replies View RelatedI have a spreadsheet (Need Data.xls) that needs to be filled out with a couple columns of data.
This data lays within 338 spreadsheets which have many items and may only have 2, or 3, or 50 that belong on my Need Data.xls spreadsheet.
I have a tab in Need Data.xls named "DIR" which has a list of 336 excel files that need to vlookup'd into.(not a separate file) They're all setup with this format: