How to use offset in combination with match and vlookup. Well I think I have to use Offset to find the value ( cell with time in it).
I have in my workbook 3 sheets: Sheet1, Sheet2 and Agents.
In 'Sheet2' every week I upload a report with persons and every person has a certain amount of time behind their name.
In 'Sheet1' I want to get (load) the data: the person and time from 'Sheet2'.
In 'Agents' I only match the names. That's because the names in the report I upload in 'Sheet2' have a different lay-out then the ones I use.
The matching and to get the names correct in 'Sheet1' Is no problem. Though I get stuck with the cells where the time is placed in the report I upload in 'Sheet2'.
The persons are in Column C ( C7, C26, C45, C64 etc) but the value I also need to get is not in line behind the names. It's In the 7th row under the name and in Column L.
Example:
Wiebe (C7) time ( L14)
Gary (C26 time ( L33)
Kay (C45) time ( L52)
What I use to match the names and get data is this formula.
I have attached an example s/sheet. Basically this is an excerpt of the data that sits in a pivot table. What I want to do is from another sheet query this data. I don't want to use another pivot table as they are quite hungry in terms of memory and the data source we have is quite large. In essence what I want to achieve is in cell G2 the user enters a code. A function (vlookup?) will then scan column A to find that code.
The function then needs to look across and sum the total of Requests and Responses for all the dates. Whilst the dates may change, the number of dates will remain the same. Once it has summed them it needs to return the totals to cells G4 and G5. Additionally it needs to fill in the relevant total (offset?) for the corresponding week as detailed in columns H-AH. It seems quite a simple lookup issue but I am not very versed in nested lookups. I have looked around and it seems INDEX woudl do the job but I am at a loss on how to construct this type of function.
What I want to do is the following, I have two sheets, one where the data needs to be filled and the second where the date needs to be looked up. In Sheet1 I need to find a date for each of the NR2 and NR1 combination. But in the second sheet there are multiple NR1 occurences and also single occurences. So if there is only one, I need that date, if there are several I need the average of all the occurences for NR1, not taking into account the N/A ones.
(some examples from the file) NR2 NR DATE 100707987121951 100702347121960 100707750121960 100707721121960 100702422121960
[code]....
So for example, NR1 121965 has two dates, 03/09/2002 and 27/01/2004, so here it should calculate the average of these two and put that average in the first sheet.
I was thinking of something like IF(MATCH(?) gives one result,put that with vlookup, else AVERAGE of all MATCH that are not N/A)
On the attached spreadsheet, i am trying lookup the selection name in column A on the summary sheet and search through column A of the other three worksheets adding together the three answers but something isn't working as the end answer is incorrect. I have tried vlookup and also tried combining offset/match but neither way i am getting the correct answer. I removed a lot of the data to simplify things and changed the figures to fictional ones for privacy reasons.
I am trying to sum 12 columns based on looking up a reference that is in one column. Basically I have 2 files where on both files Column A has a G/L account number. On the data file I have credits for each month going from column C to Column O. On the other I have one column where I want to bring in the sum of all the months based on looking up the G/L number in column A.
I have several months worth of data, lets say January to December in cells A1:L1. In the rows underneath I have data, however, it is several "sets" of data. For example, I have data in A1:L10 (the first batch) and another batch in A21:L30 and so on. So you can see that there are rows between each batch of data (this must remain thus).
What I would like to do is set up a formula that reads along the the dates at the top, then reads down to the batches of information only, and then ranks them.
Ultimately, what I want is to tap in say "September" and then in a table I want to have the top 10 ranked in order from the batches of information from the September column, and this will change according to the month I tap in etc.
I think it may be some combination of Index / Match & Rank, but I am struggling with the Rank formula applied to non-consecutive ranges!
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).
I'm trying to lookup a string in a column from another sheet and than return the row on which that string was found. I have two files, one in which my OFFSET/MATCH worked and another in which they didn't work properly.
My problem is this: I have multiple instances of a string in a column and i want to get all the info on that string (the rest of the columns). Let me explain in examples.
In column A on Sheet 2 i have
4 2 4 2 3 4 5 3 2 2 2 2 4 1 3
I need to have in Sheet 1:
4 2 4 4 1 3
Where the first 4 (bolded) is the reference value.
In sheet 1 i have a column A2 which is the reference. In column B2 i have =MATCH(A2;'Sheet 2'!A1:A16051;1) - this worked on a file but returned a bad value on the other (donno why)
In the second file i used =MATCH(A2;'Sheet 2'!A2:A10552;0)+1 which returned the good value.
For the second value (row 4 1 3) i used =IF(OFFSET('Sheet 2'!A1;B2-2;0)=A2;OFFSET(Sheet 2'!A1;B2-2;0);0). This worked well on the first file but didn't work on the second.
I got the commands above from someone that used to update this file so i don't know exactly how they work
I have in col J a series of values, these can be negative and positive numbers of any value. In col D I have the time (starting at midnight each day) that the values occured in col J. I am trying to look down col J until a value >= 50 is reached, I then want to look across at the corresponding cell in col D to find out what time this event occured. So, if a value of 50 or more is found in cell J232 for instance, I want to look across at cell D232 and extract the time found in that cell.
I have one condition which needs to be matched. This code I wrote can find the first instance and then offset data to another worksheet accordingly.
My question is whether than can be done for every other instance that appears on my sourcesheet 'Invoicing'. I have considered putting a loop in, but this will only find the first instance over and over again which is pointless.
I'm having trying to set up a new workbook to pull selected results from an existing one. I am trying to figure out the formula/function for cells B2:D3 on workbook2:
EXISTING WORKBOOK1: (Col A is dynamic range 'name', Col B is dynamic range 'product', Col D is dynamic range 'mtd') A B C D Joe P1 7 P2 1 P3 2 Total 10 Jill P1 3 P2 4 P3 1 Total 8
NEW WORKBOOK2 A B C D P1 P2 P3 Joe 7 1 2 Jill 3 4 1
I entered the following as an array formula in cell B2 and it works fine to return the proper result of 7: =INDEX('workbook1.xls'!mtd,MATCH("Joe"&"P1",'workbook1.xls'!name&'workbook1.xls'!product,0)) This does not work to return the results for P2 or P3, I am assuming because I need some sort of an offset for the different rows in workbook1.
I am hoping someone can point me in the right direction here. First I want to locate the proper name in workbook 1 and then the proper product under that name and finally pull the mtd col D result for that product and name.
I'm trying to create a formula that would return the value of the column next to it. Looking at the below example, in a new cell I would want to be able to return the value 7 based on the fact that it is in category b.
I would think an if/offset/match function would do this but I can't seem to figure it out as it may require nesting.
I am using an offset match formula to look at a named range of data and create a validation list of property address's based on certain criteria in cell d1.
My question is it appears the information which is status information seems to have to be sorted, otherwise the list doesnt work properly.
is there another formula to accomplish this with out having to sort the criteria in cell d1 ??
This is the formula i am curently using, it works great if "ListStatus" is sorted and not if unsorted.
I found the following formula, with different sheet and cell references, on someone's site: =OFFSET(CurvCalc!$E$12,MATCH(MAX(CurvCalc!$E$12:$E$10000)+1,CurvCalc!$E$12:$E$10000,1)-1,0)
I googled for the last couple of days but can't find this site again. I have found several that come close like Dave's at http://www.ozgrid.com/News/excel-dynamic-ranges-vba.htm
Any way I could SUM a YTD using VLOOKUP and OFFSET? In another worksheet, I need to reference Account Income #, go to the worksheet shown below, find the Account Income number, Offset and sum of the columns in that row to the YTD figure.
i have a table with customers and the rentals they have made. in the table it has their customer number and then the item code of the item they have rented out.
i want to have a table that brings up the item codes of what they have rented but with a vlookup it only brings back the first item they have rented and not the ones after that.
I have a data set that is laid out like this below:
Jan 12 Feb 12 Mar 12 Apr 12 May 12 Jun 12 Jul 12 Aug 12 Sep 12 Oct 12 Nov 12 Dec 12 Revenue $1000 $1000 $1000 $1000 $1000 $1000 $1000 $1000 $1000 $1000 $1000 $1000
On another sheet I want to find the Revenue and sum the Months Jan 12 to May 12.
So I would like to have my answer read $5000.
I am having a hard time doing the Vlookup and summing a range at the same time.
I am having some trouble getting a formula to work. I am building a report that pulls figures from a pivot table in another workbook. I am using a vlookup with match function to get the column index to find the relevant data I want. Where I need to add two columns together I am using sum, with the vlookup & match formulas nested in them e.g.:
Where: F13 = Employee number Column C on the pivot 156 workbook is where the employee number is based. The Match formula is then getting the column index from the column headings of the pivot table ie. "FAID"
This in itself works fine, as long as it finds a match in the column headings. This is where i get the error as in the above function "COMD" is not in the pivot table. However I need to keep it included as it may appear on a future pivot table. Is there a way of getting the sum function to complete even though later in the formula it can't complete the vlookup? So it will ignore it, or assume the value is zero if it can't find it? The formula probably needs to do this for all the vlookups as some headings may drop off in future pivot tables.
I am trying to match data from 2 independent sets, formatted slightly differently so not sure which function would work best for me. From the attached file, I am trying to match the date and time stamp (in cell A1) with that from the other data (in this example in cell E1) and return the data (from cell F1) to cell C1. So basically any date and time stamp before 04/03/09 04:00 will return a value of 44 (this value should appear, therefore in cells C1 - C30)
In the attached workbook, the following formula appears in column B of the "dropdown" tab. I want to basically do a two criteria lookup, using the values in column A and the value of cell B1, which is a dropdown based on a dynamic range consisting of the company names on the Discounts tab.(starting with cell E1, extending rightward) To make this easier for testing purposes, I'm curretly using cell E1 instead of B1, which is just text.
It seems like this ought to work, but somehow it doesn't:
I am having a heck of a time coming up with the proper combination of functions to return data to my worksheet.
Attached, you will find a screen shot of the pages I am dealing with. Here is what I am trying to do:
On the page named Update Master, I am trying to put formulae into the highlighted cells C2:C11. Each cell will contain a formula to MATCH the team name in UpdateMaster!A:A with a team name in the chart found on worksheet WEEK ONE. Then, I want to return the value found 8 cells down, and 1 cell to the right of that MATCH. Here is a specific example:
In cell Update Master!C2, I need a formula that will MATCH the value in Update Master!A2, , with a value on the WEEK ONE! worksheet. (In this case, 2EZ. After locating the match, I want to return the value found in the bottom right of that players weekly score box. (WEEK ONE!C9...in this example).
I can't make a direct reference to the data...because the value in Update Master!A:A will change after each week. The team names will be sorted in alphabetical order after being sorted by the value found in Update Master!AE:AE...(each player's accumulated score).
I have tried so many combinations of MATCH, LOOKUP, OFFSET, INDEX, ROW...etc., and I keep getting VALUE# or NA# errors at some point in the computation. Maybe one of you can point me in the right direction.
I have a problem with an offset match function. What I want to do is look at the value in cell C3 and then match it to a location in a named range and then return the value 8 rows above. The problem is the named range contains non contiguous cells i.e. D10:AE10, D22:AE22, D34:AE34. So if the value appeared in the area D10:AE10 it would return the value in the matching column row 2. How to do this I have tried lots of combinations but they just come out #N/A.
on sheet 1 I have a list of race car numbers (20K, 15W, 2) in A1:A50 (may be more or less than 50) Next to them in B1:B50 are the point values they earned for a night of racing. 100-97-94 etc.... these are points they have earned for a night of racing that week. in C1 I have how many column over I need to write to (ex. 4 for Column "D" on sheet 2)
On sheet 2 I have all the total for each week. example A1 B1 C1 Car 4/22 4/29 20K 94 90 15W 97 100 ......
What I need is some code to go down the driver list on sheet1 and write their points in colum D on sheet 2 when it finds the appropriate car number. If the car number does not exist then add the car to the bottom of the list (A50) or whatever, and write the points 4 columns over.
Is it possible to nest the two functions of match & offset. I am working with two worksheets. One is a constant the other is building. I want to match a text from the first spreadsheet in the second sheet (otherwords I want to find that exact within the other spreadsheet) then I want to retreive the offset value to the right of the matched cell and place it in the constant worksheet where the formula is.
For Example
Sheet 1 Sheet2
Company: First Name: Company: Name: Adamack's ? Adamack's Patrick
So if I put the formula where the ? mark is, I want to match the first company name on sheet 1, from the range in sheet 2, then I want to get the offset from that match 0,1 to give me the name patrick.
