Offset / Index / Match - Using 3 Functions To Search For And Return Values From Data Sheet
Jan 19, 2014
Trying to grasp the concept of using these 3 functions to search for and return values from a data sheet.
The attached spread sheet has performance data for a group of employees.
What I need to do is find a particular employee then return a value for one of the category's.
For instance, I need to find "10TE03 ANGIE HOLLIS" Parts Usage on color or cell C10 in the attached sample.
Sometimes new category's are added to column A adding to the number of rows so a simple offset is not reliable.
Once I get that working, I then need to use a named range to total and average different data points for groups of employees by teams.
Maybe Offset-Index-Match is not even the way to go here?
View 7 Replies
ADVERTISEMENT
Apr 12, 2013
I receive data (A1:E2) which is sorted alphabetically accordng to name:
AaronBobConDanEd
3-Jan-135-Jan-133-Jan-131-Jan-132-Jan-13
I use the SMALL function to sort the data in date order e.g: A6= =SMALL($A$2:$E$2,1) = 1/1/13, B6= =SMALL($A$2:$E$2,5) = 2/1/13, etc:
1-Jan-132-Jan-133-Jan-133-Jan-135-Jan-13
I then use the INDEX & MATCH functions to place the names under the sorted dates e.g. A7= =INDEX($A$1:$E$1,MATCH(A6,$A$2:$E$2,0)) = "Dan", B7= =INDEX($A$1:$E$1,MATCH(B6,$A$2:$E$2,0)) = "Ed", etc:
1-Jan-132-Jan-133-Jan-133-Jan-135-Jan-13
DanEdAaronAaronBob
The problem I have with this formula is that if there is a duplicate date (i.e. 3/1/13), the INDEX/MATCH formula returns a second "Aaron" rather than "Con".
How to modify this formula so it will return "Con" and not a second "Aaron"? Please note that duplicate dates may recur across the row.
The formulas always assume the data is in multiple rows rather than multiple columns.
View 2 Replies
View Related
Feb 4, 2014
I have two sheets of data , sheet A and Sheet B.
Sheet B contains a column called "Name" and for each name, and for each name there are corresponding numbers. In sheet A, I have a "list of interest" in column A. What i want to do look through the data in sheetB to find a match from the list of interest and return the corresponding letters, located in column A.
I have filled out the first two rows of results that should be returned as an example.
One idea i had was to put a vlookup formula in each column result 1 to result 6 so i can catch all 6 "Serves" columns from column B, but there may be duplicates in the serve columns and vlookup only reports the first match.
View 3 Replies
View Related
Jan 29, 2014
I have 3 calculations I would like to make based on data in the spread sheet and I can't seem to get them to work with data from the two separate columns.
I tried a few of the index match max formulas I found here and could only get them to work with one column of data.
I have the spread sheet attached and the 3 calks I want to do are blank on the bottom.
I am using Excel 2011 for Mac
View 7 Replies
View Related
Aug 26, 2009
I have a list of data that I want to average the number in column B when there is a match in column A
COLUMN A | COLUMN B
alpha | 100
beta | 200
gamma | 300
alpha | 400
beta | 500
What I'm looking to do is return the MEAN of values in column B where column A = alpha. Though, I would settle for returning a list of the values that match the criteria. Index/Match combinations don't seem to be helping me because they only return the first match, I'd like to return all matches.
View 4 Replies
View Related
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:
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.
View 9 Replies
View Related
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.
View 3 Replies
View Related
Jul 2, 2006
I am working on a scheduling system and am trawling through some old notes of some two or three years. I came accross two formulas, which I must have used a while ago. they are a little more baffling that what I have recently used, but I must have referenced them for a reason. Normally I write down explanations of the formulas in blonde, so that I can understand their reasoning at a later date, however, I can find no reference to them. explain what they actually do in a logical fashion. I have been looking at this for four hours, filling in date ranges and cells that they refer to, but cannot make sense of them.
=MATCH(E6,Caljay) + (MATCH(E6,Caljay)=MATCH(E6,Caljay))
=INDEX(Caljay,F6,1)-(INDEX(Caljay,1)-MAX(INDEX(Caljay,F6,1),E6))
View 3 Replies
View Related
Nov 28, 2012
Attached is a sample workbook, but essentially what I'm looking to do is automate the process of searching through a data set where the value of interest (in this case, names) often has multiple entries, with different values attached to each instance.
I would like to be able to get a list of all values in a given column that match a specific name in another column.
Currently I'm using a basic INDEX/MATCH search just to see whether the data exists at all, but that's only half of what I have to do here, and I'm totally stumped on how to get a comprehensive list of all matches.
For reference, if you look at the sample, what I need is a list of all values in the "CPT" column that match the name searched for in the first column.
The actual data set size is at most 3-400 entries, if that makes a difference in how to approach this.
CPT Sample Book.xlsx
View 4 Replies
View Related
Jul 11, 2012
I have encountered a situation where I need to essentially accomplish a reverse Vlookup (using index match) and return multiple values.
View 1 Replies
View Related
Aug 29, 2009
I am learning to use the index and match worksheet functions.
I read through some examples and tried to set up a spreadsheet as attached.
For some reason, it is working only partially. For some cells the value is returning correctly and in some it is returing #ref.
View 2 Replies
View Related
Jun 1, 2008
I would like to use a custom function or excel formula, if already exists, that finds the text "12 months" and then returns the value of the column that holds that value.
View 9 Replies
View Related
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.
View 10 Replies
View Related
May 26, 2008
I want to write the following function using VBA. =index(B2:D8, match(lookup_value, A2:A8, 0), match(lookup_value, B1:D1, 0))
View 8 Replies
View Related
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?
View 8 Replies
View Related
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.
View 9 Replies
View Related
Aug 1, 2007
I have all my data on sheet 2 which contains tables that relate to information on regions, manufacturers, etc so it can be a large amount of tables which will change from month to month depending on a monthly list.
For example, if “Region 1” is in cell B134 – the resulting data I need to pull out will be contained in C138:G232 ...or ”Region 2” which is in B235 – table info is in C239:G333, etc, etc.
On sheet 1, I have a cell (L7) that is populated by another formula. This cell is effectively my lookup to pull out information from sheet2 to populate cells in (sheet1) AE119:AH212 - same sheet as the reference (L7).
I need to look up the reference/resulting value that is in L7 on sheet1, find and match against the values in column B on sheet 2 and then pull in the corresponding table information.
If the value in L7 was not changing at all I could do.. in cell AE119… =OFFSET(sheet2!B134,4,1,1,1) etc etc
... but I am at a loss as to the value in L7 changing and incorporating a lookup… or maybe I am looking at it wrong..?
I have looked up match, offset, index and lookups on the forum and have managed to confuse myself even more. I have even tried taking some of the example formulas and amending with my references but to no avail.
View 10 Replies
View Related
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.
View 3 Replies
View Related
Apr 16, 2009
I know there are many posts concerning this, but after scouring, I couldn't find one that fit my situation. I have a total of six worksheets, I am only concerned with two worksheets.
Worksheet (functions!)
This one has a list of numbers formatted as general. (Column G)
Is actually a formula/macro that outputs a number... (didn't know if this mattered?)
View 6 Replies
View Related
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.
View 9 Replies
View Related
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.
View 1 Replies
View Related
Apr 8, 2008
I have to match table column name with the column name in the sheets and get the values from column 6 in sheet2 if "# of values" in sheet1 matches with "Counter" of sheets for the same column.
if ("Table Column Name sheet1"="Column Name sheet2" then
if("Counter sheet2"="# of Values sheet1" then
return "Total # of rows Sheet2" from same row.
I am using Excel 2003.
Sheet 1 ....
View 10 Replies
View Related
Jan 21, 2014
See attached file, "Rate Sample Index-Match Formula".
I need a formula to return the value at the cross section of two (2) lookup values that match. This formula will be input into column D under, "RATE" on the 1st tab, "TEST FILE".
In the 1st tab, "TEST FILE" there are a series of columns as follows;
A = Service
B = From
C = To
D = Rate
In the 2nd tab, "RATES" there is a series of rates with drivers From (green) & To (blue)
The formula needs to do the following;
1. Lookup the "From" value in column B on tab, "TEST FILE" and match to column B2:B59 on tab, "RATES" both highlighted in green
2. Then Lookup the "To" value in column C on tab, "TEST FILE" and match to row C1:BH1 on tab, "RATES" both highlighted in blue
3. Then return the value at the cross section of the match "From" (point 1 above) & "To" (point 2 above) in range C2:BH59
For Example;
The rate From SYD To CBR = 0.33. I have highlighted this in yellow on both tabs to show where the formula needs to lookup the data to return the answer.
Additionally, if we were to add service as an additional lookup match how would this work?
View 3 Replies
View Related
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
View 9 Replies
View Related
May 8, 2007
I have a multiple column spreadsheet (Call it- "Money") whereby I need the data identified based on a list of account numbers and return this data to a new sheet.
In "Money" I have:
IE; columns B, C respectively have cust #A100 & 20.00
columns E, F respectively have cust #B100 & 40.00
columns G,H respectively have cust #C100 & 60.00
Etc.
(above for illustration-there are 100 lines of data in these columns with varied account numbers and respective dollars)
So what I have now is a new sheet I have named "Control". I have listed all my account numbers like A100,B100 etc. in column A. These are the account numbers for ident purposes.
I need the data entered in "Money" identified by those columns B,E,G with respective amounts from C,F,H and based on the list I have in "Control" whereby in "Control" if A100 is listed in column A then the figure to be returned in column B is all the data bits found in all columns C,F,H from the entire sheet "Money". Tough to explain but ie below....
View 17 Replies
View Related
Aug 5, 2014
I'm trying to build an Index-Match formula to retrieve a number from a "data" sheet onto a "summary" sheet.
In example spreadsheet, I need to retrieve the stock price on a specific row from "data" sheet, and as an alternative, the most recent price.
View 6 Replies
View Related
May 4, 2013
Create a macro button in 'Spreadsheet 2' that searches 'Spreadsheet 1' for updated information specific to a certain criteria and adds it to a new row in 'Spreadsheet 2.'
There are three sheets
Prospects (where all original data is entered)
Actions -Bob (Bob's new Prospects are added to this sheet)
Actions -Frank (Frank's new Prospects are added to this sheet)
So in this example Row 6 in Prospects (Constituents, Rating, Manager and Solicitor would be added to ACTION - Bob's sheet on Row 5
and
Row 8 in Prospects (Constituents, its Rating, Manager and Solicitor would be added to ACTIONS - Frank sheet on Row 5
Here is the example spreadsheet - Prospects and Actions.xlsx
View 3 Replies
View Related
Sep 28, 2012
I'm trying to search a column to match the value in textbox1 then return to the userform the values from the same row in columns A to textbox2 and so forth.
I can get it to find the value but am struggling to get the data back to the userform
Reason for this is so the user can search an Id, get all the data back on the order before changing the Id number
The code im trying to use is
Code:
Private Sub CommandButton1_Click()
Dim lr As Long, i As Long
Dim x As Variant, y As String
Dim Found As Range
x = TextBox1.Value
y = UserForm2.TextBox2
[Code]....
View 1 Replies
View Related
Jul 23, 2012
I have 2 workbooks in Excel 2010, each contain just 1 sheet. (see attached) I need to compare on sheet 1, cell D1 and column A:A (this column will be much longer), with the data in columns C:C & A:A on sheet 2, if a corresponding match is found, the data contained in column D on the same row on sheet 2 is written to the cell with the matching data in sheet 1.
My attempt is in cell D2 on sheet1.
View 5 Replies
View Related
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.
View 5 Replies
View Related
