# Match Offset Combination

Apr 6, 2009Can Offset be combined in an Index Match Match formula as per the attached sample?

View 4 RepliesCan Offset be combined in an Index Match Match formula as per the attached sample?

View 4 RepliesADVERTISEMENT

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.

=INDEX(Sheet2!$A:$L;MATCH(VLOOKUP(Sheet1!$A2;Agents!$A:$E;5;0);Sheet2!$C:$C;0);MATCH(B$1;Sheet2!$1:$1;0))

Is it possible to use Offset ( or something different) in this formula to also get the cells with the time ( matching with the right person)

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.

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.

View 4 Replies View RelatedI 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!

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)

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

=IF(MATCH($A7,$BX$15:$BX$112,0)>150,"",OFFSET($BX$14,MATCH($A7,$BX$15:$BX$112,0),1))

If there is not match, I get #N/A in the cell. I would prefer to get a value of zero (or ""). I've been experimenting with ISNA and ISERROR.

I'm struggling with this.

Sheet2.A1 = Cat

On Sheet1, I want to find Cat in Column C and then Offset down 2 rows and over 57 columns

Put result on Sheet2, B2

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.

View 2 Replies View RelatedI 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.

categoryvalue

a1

a2

a3

a4

a5

a6

b7

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.

=OFFSET(AddressDisplay,MATCH($D$2,ListStatus,0)-1,0,COUNTIF(ListStatus,$D$2))

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

http://www.cpearson.com/excel/lookups.htm

http://www.cpearson.com/excel/lists.htm

and Microsoft's at

http://support.microsoft.com/default...;en-us;Q152407

=INDEX('Data Dump'!$C:$C,MATCH('YTD Detailed'!B$2&A40,'Data Dump'!$G:$G&'Data Dump'!$D:$D,0))

I have to above array formula, i am looking to nest an offset within it. That looks up two columns to the right, but im having trouble.

Can an offset be added to an index?

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.

View 2 Replies View RelatedIn 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:

=OFFSET(Discounts!$B$1,(MATCH($E$1,Discounts!$C$1:$AA$1,0)),(MATCH(A5,Discounts!$B$2:$B$200,0)),1,1)

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.

View 3 Replies View Relatedon 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.

I have three columns of data in columns A,B,C - there are formulas in these columns that either produce a numeric value or return blank.

I have created Dynamic Named Ranges for each of them and have entered the following formula:

=OFFSET(Working!$A$1,1,0,MATCH(1E+305,Working!$A:$A,1))

My issue is that this formula is looking one row past the last numeric entry in the column (a blank) which is leaving a blank space when I graph this data.

Below is a table of data I am trying to sum. In the first column, is the account code, second column has the Hotel number code, third column is the description and the 4-6 columns have amounts for March, April and May.

I am trying to sum the data for March, April and May for account 107000 using this formula, but it is only picking up the first row.

=-IFERROR(SUM(OFFSET(PnL!$C$9,MATCH($A29,INDEX(PnL!$A$10:$A$500,0),0),1,1,ControlMonth)),0) to sum

FYI:

PnL!$c$9 = is the block referred to as C9 below

$A29 contains 107000

PnL$A$10 is the block below A9 below

ControlMonth is currently set to 3

[code].....

I have created some code to look in a cell to see if the name matches that which has been input in a drop down list on a combo box on an input form and then copy the value of column 10 in the same row. I created a public variable for that name as txtTenant.

What I wanted to do was to then write the code that if the first cell did not match the name selected (txtTenant) that it would then drop down one cell and compare it and keep going down until it finds a match, right down to the end of the column of data if necessary.

My coding is not working - it stays at the first person it finds in the first cell. I am not sure if I should be using For...next loops or loop.....

This is the

Sub FillData()

Application. ScreenUpdating = False

RentRow = 2 'sets the first row as row 2 in the data sheet to copy from

NewRentRow = 7 'seta the row to copy to in txtTenant

i tried using the lookup but it gave a different result. i want to search a value from sheet1 A to sheet2 A and copy the remarks from sheet2 B and paste it to sheet 1 B and if not found leave it blank.

here's the attach file i just trim it down.

A B C D E F

Latest Status

Latest Date

Current Status 1

Current Date 1

Current Status 2

Current Date 2

#VALUE!

Sun,16-Feb-14,11:0 PM

Take1

Sun,16-Feb- 14,10:41PM

Take2

Sun,16-Feb- 14,11:02 PM

My formula =IF(B3="","",OFFSET(A3,MATCH(B3,B3:F3,0),0,2)) in A2 is giving me #value error.

The correct solution should be Take2, while matching the condition from B2 latest date.

Sheet Attached. File Name-Latest.xls Zippyshare.com - Latest.xls

I have the following formula in a cell:

=SUM(OFFSET(INDEX(J:J,MATCH(9.99999999999999E+307,J:J)),0,0,-M9))/M9

When the workbook with this formula is open it slows down other workbooks quite a bit.

If I run code in another workbook without the workbook with the formula open, calculation takes 0.099 seconds. If the workbook with the formula is open, the same code takes 2.24 seconds.

How could I change/replace above formula to average the last n (cell M9 value) cells in an ever changing column.

How can I modify the following formula to find the contents of reference cell, for example if the cell reference (G$3) is equal to 9 I want it to use 8 instead.

Code:

IF(ISERROR(INDEX(tra!$E$2:$E$1100,MATCH(G$3,tra!$C$2:$C$1100,0))),"",

INDEX(tra!$E$2:$E$1100,MATCH(G$3,tra!$C$2:$C$1100,0)))

I tried using G$3-1 but this doesn't appear to work.

Attached is a sample file.

I can't figure out a formula that will match either 1,2,or0 in column K and offset to corrosponding value in column B. Then average of all values that came up.

how to accomplish this. VBA is acceptable, but formula is prefered.

0.644

0.627

0.641

[Code]....

Copyrights 2005-15 www.BigResource.com, All rights reserved