# Match Offset Combination

Apr 6, 2009

Can Offset be combined in an Index Match Match formula as per the attached sample?

## Offset In Combination With Match And Vlookup?

Apr 24, 2014

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)

## Vlookup, Index, Match, Offset, What Combination Should I Use?

Sep 22, 2009

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.

## Sum Index And Match Combination

Apr 8, 2009

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.

## Index / Match / Rank Combination

Jan 7, 2009

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!

## Combination Of AVERAGE With VLOOKUP And MATCH / INDEX?

Aug 14, 2014

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)

## Offset/match

Mar 2, 2009

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

## Getting Rid Of #N/A With MATCH & OFFSET

Oct 24, 2008

=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.

## Match Offset On Different Sheet

Feb 7, 2014

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

## Match Values And Offset

Feb 2, 2009

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.

## Exact Match And Offset More Than Once?

Feb 17, 2009

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.

## Index Match Offset

Jun 30, 2006

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.

## Match Or Offset Function

Jul 10, 2008

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

## Offset Match Formula

Jul 2, 2009

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, Match, Max Formula

Sep 22, 2006

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

## Offset Index Match Array?

Jun 3, 2014

=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?

## Vlookup Or Offset/match Formula

Apr 16, 2009

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.

## Offset Match (two Criteria Lookup)

Aug 7, 2009

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:

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

## MATCH, LOOKUP, OFFSET, INDEX

Nov 5, 2006

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.

## Offset / Match With Named Range

Mar 7, 2014

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.

## Lookup - Match - Find - Offset

May 2, 2006

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.

## Nesting Functions Of Match And Offset

May 31, 2007

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:

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.

## Dynamic Range Using Offset And Match Functions?

Aug 26, 2012

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.

## Sum Multiple Columns And Rows Using Offset And Match

Mar 25, 2013

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].....

## Macro: Search Down Column & Match Then Offset

Nov 19, 2006

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

## Find Match In Column & Offset For Return Value

Sep 2, 2007

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.

## Offset / Match - Formula To Return Left Column Value On Same Row?

Feb 16, 2014

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

## Offset Index Match Formula Slowing Down Excel

Dec 28, 2009

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.

## Index Match Offset - Find Contents Of Reference Cell

Jun 21, 2012

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.

## Match Multiple Occurrences And Offset - Then Average All Values Found

Nov 11, 2013

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]....