I'm having serious difficulties with getting together a formula for a little bit complicated lookup.
I have 2 tables. In the first table I have categories in the first column (e.g. red, blue, green), in the second column I have dates (DD.MM.YYYY). And in the third column I would like to get the values from the second table.
In the second table I also have categories in the first column, dates in the second column und values in the third.
I would now like to get the values from the second table that coincide with the categories. And in addition the dates should also be equal to the date in the first table OR the date from the second table should be as close as possible but BEFORE the date in the first table.
Example
Table1
Red 07.08.2005 ???
Table 2
Red 18.04.2005 three
Blue 11.06.2005 one
Red 06.08.2005 four
In this example the value that should be looked up is "four".
I need to look within column A (which contains text strings), match label in column c (which contains text for label), and return result within column B (titled category label).
Essentially, I want to label my data based upon a specific element found within column A with a label list in column C.
I have attached an excel file that shows the current problem. Nested if(isnumber(search( statements can perform this, but I yield too many arguments error after three labels?
Isn't there a way to do this with Vlookup or Hlookup?
I need a formula to count list items if the date is between a specified parameter. I am trying to count how many help tickets were logged for a particular group or bureau during a specific week.
How many AA's between 12/1/2008 and 12/8/2008?
Bureau Date Opened AA12/1/2008 AA12/1/2008 AC12/2/2008 AA12/6/2008 AB12/12/2008 AE12/15/2008 AC12/16/2008 AD12/17/2008.........................
I know how to count a list of items when you identify what you want it to count (I11 (Access)) and where (Application (Named Range)) but I can't figure out how to include the date parameter.
I J Access23 =COUNTIF(Application,I11) Acrobat2 ADMIN4
I have spent a good amount of time trying to create the below using botched IF functions, SUMIFS, SUMIF formulas etc.
Basically I have a sheet (called Sheet1) with the following columns: A - Date (by individual day DD/MM/YYYY) B - A catagory (so using the old example, Apples, Orange, Pears) C - Price.
I would like to be able to create a formula, whereby I can sum the Total sales for each catagory across each month onto a summary sheet (sheet2).
I would use pivot tables, however am not sure how i can make all the require data appear permanently in the best way, especially if there is a one liner that can be done more easily. So far I have managed to create a sum dependent on month, but have been unable to encorporate the catagory.
I need to create formulas that reference a single date and output date ranges. The objective is to have a person input a Monday date in any given month and receive a four weeks out worth of dates and ranges. For example: In a lone cell, the person inputs 10/13/08. Automatically, the sheet produces the next full week range: October 19 – October 25 in a single cell and also produces a cell for each date. Example: Sunday 19, Monday 20, Tuesday 21, etc…. It should look like:
and then repeat for three more weeks. I thought I had it figured out until the month changed. The dates continued in October instead of adding a month. This report will be ran weekly, so simply adding a +1MONTH to some cells will not benefit me as I’ll have to change the formula every week. I want the formula to compute the data without any manipulation over the next several years. The only change will be the Monday date.
I need to look up the highest value in a column and then return a name in the same row as the value is found. I been playing with max and offset but they are not working for me.
I'm trying to cross-reference three sets of criteria to find my result; however, I can't get the VLOOKUP function to perform correctly for this need. Maybe I'm using the wrong function.
I attached a simple workbook to highlight my challenge. The over-simplified language that I included in the attachment, which highlights my problem, is as follows:
IF: (B5=Sheet2!B4:B12) ---> This line finds the correct row to reference, on the Control sheet. (year(C2)=Control!C3:E3) ---> This line chooses the correct annual column on the Control sheet, for the given month (e.g., use 2014 assumptions for a 2014 month). (month(C2)=Control!E14) ---> This line counts the expense only in the month when the expense recurs (e.g., April 2014, April 2015).
THEN: SUM(Control!C4:E12) ---> This line finds the specific number based on the criteria above.
See attached a sample from a larger workbook I am working on. What i would like to do is in the Rec tab column G, keep the references from columns L & M as the Table Array and Column Index Number. I have =VLOOKUP(F:F,L:L,M:M,0), I would like to have =VLOOKUP(F:F,whatever tab reference is in column L as table array,whatever number is in column M as index number,0). I have included what I would like the data to look like in coulmn H.
in the above table I need to the sum of WC for each Language code & batch no. eg. for Language "id" & batch 1 I need to get the SUM of WC corresponding to the criteria.
I have a worksheet(Shed) and the range where numbers will be entered are B3:E46. In another sheet(Location) I have a in column A "Shift #", B "Shift Bus #" C "Location". what I am attempting to do is when a bus number is entered into Shed! ie.. b3=900, d15=350 etc.... Location! would lookup ie.900 in the Shed! and match it to Location! (column B) and from there provide the cell ref in column C. So I could then print Location! that would give me in Shift # order where each Bus # the shift is assigned to.
I want to create a series of VLOOKUP commands with different lookup tables whose names are concatenated from two different pieces. Here's what I'm trying to do:
LUTWK01, etc., are named ranges. I have the text "WK01", "WK02", "WK03" stored in other places, and would like to be able to concatenate "LUT" and the particular week (e.g., "WK01"). The formula that I've created, which isn't working is:
VLOOKUP(x,CONCATENATE("LUT",A1),y, false) where cell A1=WK01.
When I pull out just the CONCATENATE portion, it resolves to LUTWK01, but apparently this is not recognized as a range name. Any idea as to how I make the VLOOKUP formula recognize a concatenated range name? Or is there a better way to do this?
I have attached a spreadsheet with a small amount of the data I need to use. This is the distance between customers in kms. I need to be able to have excel look up the cell reference for distance between two customers though they will not always be the same customers every day in the same order.
i just want to use vlookup and hlookup to give me the row and column headings for 30 to 40 sesor at a time.... but i keep getting #n/a whenever the functions meet a table of more than one column! (reduced workbook attached)
it should surely be simple to get this data - but i've struggled to no avail. I thought that having the four separate worksheets was the problem - but i haven't had any look even when i dump the data into a single worksheet!
i basically just want excel to return the cell reference of a sensor number which exists in a table. sometimes a sensor can appear more than once, but its not very common and i could happily work around that by doing the manual search (ctrl f, find all).
any advice would be very much appreciated, i'm struggling and the number of sensors i need to test will increase in the coming months.. please help!
should i even be using vlookup and hlookup? ive tried all the other excel functions, but they don't seem to be useful?
In column A, I have dates; In column b i have security levels. I have made a table called "Security" it contains to columns, a list of security levels and no of years when each security level is required to be reviewed. the table is setup -
d1 e1 Restricted 5 etc
Example of data ie. a1 b1 c1 Restricted 1/06/2012 1/06/2017
What I am looking for is a formula to look up a1 "restricted". then lookup the security table and find "restricted" its value is 5 (years) then add the 5 years to date in b1, but place it in c1.
I have a table with three columns. I'm building a calendar on a separate worksheet and am looking up the "value" based on a calendar date. So if a date falls within any of the ranges, I'd like to return the value in column C. For example, if the date is 02/07/12, I'd like for the result to be value 1, or if the date is 04/17/12, then I would like the result to be value 3. I've used a nested vlookup, but all that give me is the value when either the start or end dates match, but I can't get a value when the date falls within the range. If the dates were consecutive, I would simply use vlookup/TRUE, but the dates are not consecutive.
How can I reflect the correct trainee count for Day1-Batch1?
Currently columns F,G,H computes for expected, actual and total variances of trainee respectively reference to the training date J3.
Since this is a 2-day per batch even, I could not capture the 1st day trainee total of every batch because I divide the sum by 2 (2 day / training batch). The sum is only corrected on the 2nd day. Attached herein is the sample file.
Where Sheet1!B2, B3 and B4 are the fixed dates (5/15/2010, 8/31/2010 and 10/31/2010). I would like to copy the formula into multiple cells, but keep the references to the fixed dates the same. When I do a copy, paste, the resulting formula becomes:
Trying to use a the getpivotdata fomula with date as an argument, refer to with a cell. e.g. =GETPIVOTDATA( 'May Table'!A1, "SICK " & S20). where S20 is a date format
I have a pretty elaborate spreadsheet for my bills. One cell adds up all the bills due during a specific pay period using named ranges. The formula looks like this:
It works, but when I have to populate cells for upcoming pay periods, I have to go in an edit the dates manually. I'm lazy, and want to simply reference a cell with the date in it instead of typing the whole date out in the formula. What I'd like to do is this:
I am making a spreadsheet that requires multiple dates and percentages. Is there any way that I can make the percentage that is related to the most recent date weighted more than a percentage with a date from a month or two ago?
For example: X//Y//DATE 90%//85%//3-March 85%//70%//21-May 87%//90%//6-June
Is there formula so that I can have the 90% and the 87% be the most important (or weighted), the 70% and the 85% be the second most important, and the 85% and the 90% be the least important when I average them out?
I'm using the following formula which is entered by using VBA on the worksheet change event but the cell reference 'A2' is not changing for each row, i.e. A3, A4 etc.....
Code: =MAX(IF( 'Device Use - 4 month Period'!$A$2:$A$20000=A2, 'Device Use - 4 month Period'!$C$2:$C$20000))
Using cell contents in formula to Populating cells with values from closed workbooks I am trying to create a summary table of data (e.g. orders) from previous weeks. In the table I am creating, the left hand column contains the Monday date of the week to be summarised along the row. I want the cells to show the values from other spreadsheets when those spreadsheets are closed, but I want the formula to pick up the date of the week that is in column A. I have attached a sample spreadsheet to try and make it clearer.
I need to lookup the newest date that is populated and take the corresponding value for that date for each row. For example member ID m1, needs to go to the newest date and show the value for the newest date that it is populated, so it would show the number 5 in column E for 8/4/13, as that is the newest date that is populated. For member ID m2, I need to show the number 7 that corresponds with the date 8/2/13. Attached is a picture for reference.
From cell I1 i want to lookup the date from A1. Then you can drag across automatically insert each date (D1 and G1) into DATE B (J1) and C cells (K1). ie. insert value copied from each date which is located every 4th cell.
