Excel 2007 :: MATCH Or INDEX To Obtain Last Immediate Value On Changing Column
May 23, 2014
I have one worksheet. I am needing to match up column G cells (TELCEL/MULTI) with value on E3 and with value E4 and give consecutive numbers on column F depending if the matched column G with value on E3/E4 is on same date of column L and if column Q (Tipo de movimiento) appers "Ventas"; I have plenty blankcells and other values on column G.
column E
# DE EVENTO
COMPANIA
INICIO
MES
Clave del producto
Descripcion
Fecha del movimiento
Clave de la tienda
Clave de la caja
Clave del usuario
Clave del vendedor
Tipo de movimiento
row3
TELCEL
[Code] ........
I have to put 1 to first and then sum one if condition is true
1
TELCEL
TAT
may
TAT31
Telcel, Telcel tiempo aire $31
01/05/2014
01
01
3
Ventas
View 4 Replies
ADVERTISEMENT
May 22, 2014
I have two data sets across two worksheets. The first worksheet contains sales order numbers (Sheet1:column A) and other data . The second worksheet contains sales order numbers (Sheet2:column A) and the product details.
On sheet1, I had to manually duplicate a sales order number (inserting another row) if the number of units of the order is greater than 1. I then need to fetch the product details from sheet2 for each unit for that specific order number. However in sheet2, there are multiple products for one sales order number.
How do i create a lookup/match to fetch the product details for each sales order number without duplicating the product details if there are multiple products for one sales order number?
I know a simple vlookup function will return the values that it matches first and that is not what i want.
Example: Sheet1
Column A (Sales Order#) | Column B (Quantity)
0417436GPCP | 1
0417436GPCP | 1
0413412FACY | 1
0413412FACY | 1
[code].....
View 6 Replies
View Related
Jul 30, 2014
I have a workbook that has 5 worksheets. Worksheet1 is the Summary tab, Worksheets 2-5 contains data all sitting under the same headings and formats however vary in row number e.g.
Worksheet 2 - 63000 rows
Worksheet 3 - 48000 rows
Worksheet 4 - 23000 rows
Worksheet 5 - 21000 rows
In Worksheet 1 Column AQ contains a Cost Centre number which I would like to extract the parent description of in Column BI of the same Worksheet and then the child description into Column BJ. Worksheet 1 currently has 16000+ rows and grows daily.The location of the Cost Centre in Worksheets 2-4 is contained in Column A and the Parent Description in Column W and the Child Description in Column Y.I am currently using this INDEX MATCH formula to search all worksheets but as you can imagine it's tediously slow and needless to say sometimes not reporting/updating the values correctly likely because it's frozen.
=VLOOKUP(AQ2,INDIRECT("'"&INDEX(Sheet1!$A$1:$A$4,MATCH(TRUE,
COUNTIF(INDIRECT("'"&Sheet1!$A$1:$A$4&"'!A2:A63355"),AQ2)>0,0))&"'!A2:W63355"),23,0) this looks up Cost Centre in Worksheet 1 Column AQ and returns the results from Column W in Worksheets 2-4 to Worksheet 1 Column BI
[code]....
View 4 Replies
View Related
Sep 21, 2012
HTML Code: VLOOKUP(M2,'FX Rates'!$A$1:$P$199,2,FALSE)
I have lots of vlookups in my spreadsheet. Match and Index is a better method and should speed up my spreadsheet (1000s of vlookups!).
View 5 Replies
View Related
Feb 28, 2013
excel 2007. Here is the situation:
I am using the Index and Match function to lookup for two specific criterias in a different worksheet. So far, it is working well, but it gets complicated. I want to look for the criterias in 6 different worksheets based on what a certain column is saying. Here is an example:
A
B
C
D
[Code].....
So, if the continent is Asia in the column A, I want Excel to look in the Asia worksheet for the city and the venue and return me the contact information. Same, if the continent says Europe, I want it to look in the Europe worksheet or the city and the venue and return me the contact information. So on and so forth.
The formula I have at the moment is this:
{=INDEX('Asia'!$1:$1048576;MATCH(B3&C3;'Asia'!D:D&'Asia'!E:E;0);9)}
How do I incorporate the logical test for it to look for the proper worksheet knowing that all my continent worksheet have the same structure?
View 3 Replies
View Related
Mar 26, 2013
currently using Excel 2007 with Windows Vista.
I currently have a worksheet where I want to input a date (G2) and a rank value (H2) ranging from 1 to 4. The header value (B1:E1) corresponding to the date (A2:A4) and the rank (B2:B4) should be returned to I2 (currently returns #NA).
Using formula: =INDEX($B$1:$E$1,MATCH(H2,INDEX($B$2:$E$4,MATCH(G2,$A$2:$A$4),)))
which I found under: Find row, find value, then return column heading
However, the above formula does not seem to work with my date order or recurring data values of 1 to 4 over the 3 rows.
The worksheet layout is as follows:
Date
A
B
C
D
Date
Rank
Header
[code]....
The return value under Header should be C.
I have reversed the order of the dates and put sequential numbers in B2:B4 as plug variables and the above formula will return the correct Header value but I need the formula to work with the current date order and repeating rank values of 1 to 4 in B2:B4. Do not wish to use VBA.
View 2 Replies
View Related
Oct 3, 2013
Whatever formula I try is returning an error of #N/A
I have two worksheets the first with transport data - customer,collection point, delivery point, haulier and number of pallets - all this information is in columns; the second sheet with the rates show haulier, customer, validity, delivery point in columns and then pallet numbers run across a row with rates tying in underneath. i have simplified this below.
I am just trying to reference all the data and work out what the cost for the variables would be in the example the cell i'm trying to find is H8 (320) -
I have already tried the following and several modified versions:
=INDEX('RATE LOOKUPS'!$E$6:$I$9,MATCH(1,('MASTER DATA'!A:A='RATE LOOKUPS'!C:C)*('MASTER DATA'!B:B='RATE LOOKUPS'!B:B)*('MASTER DATA'!C:C='RATE LOOKUPS'!D:D)*('MASTER DATA'!D:D='RATE LOOKUPS'!A:A)*('MASTER DATA'!E:E='RATE LOOKUPS'!E5:I5),0))
[Code] ..........
View 6 Replies
View Related
Mar 15, 2013
I have two spreadsheets, one with master file with original data and one that needs to pull in the original data. My issue is all the data to match off of is in the same column, and a number of other files link to the master file, plus it is used externally, so I cannot alter it, and I would rather not create a mock/copy file. Is there a formula that can look for 2 different items within the same column? Would prefer not to use VBA, but if that's the only option I'll take it. I am using Excel 2003.
Here is an example of the setup - I would need the formula to reference off the two different items/categories in the column, so lookup off the 'St. Louis' and following that, lookup off the produce items.
View 4 Replies
View Related
Jul 18, 2014
Excel 2010: I'm working with a sheet where I would like to get accurate results on the make and model of cars sold in a given week. The workbook contains several worksheets each representing weekly sales numbers for different vehicles.
The Make-Models tab has the lists used to create the drop-down selections.
The Sales tab allows one to create an ad hoc report on the total number of vehicles sold, by Make and Model. Cell B2 is named 'SelectedModel' and used in the formula found in column E, Total Sold.
Problem: When I select Honda, for example in B2 of the Sales worksheet then select Accord in cell B3 of the same. The results returned in E2, E3 and E4 are 2, 3 and 4, respectively, instead of 8, 3 and 7.
2 Honda Accords were sold on Monday and 6 on Tuesday of the same week. My formula is only returning the first matched value of 2 whereas I would like it to return 2+6, 8 for week 1. Same goes for other weeks.
Select any other make and model and notice the problem follows. I believe the error is due to incorrect usage of the formula or incorrect formula altogether.
IF(ISNA(INDEX(Week1!E:E,MATCH(SelectedModel,Week1!B:B,0))),0,
INDEX(Week1!E:E,MATCH(SelectedModel,Week1!B:B,0)))
View 9 Replies
View Related
Apr 24, 2014
I am trying to monitor the status of a cell on another sheet and autopopulate a cell depending on that information. The formula works well until I give the spreadsheet to a 3rd party and the formula ranges change after they paste new data to the source sheet. I have tried locking and password the formula cells but they change range regardless!
Here is the formula from the first cell.
[Code] ......
It scans for a number in an adjacent cell. If the number is present on the sheet 'Test', it autopopulates the cell with a string from the source sheet. The cell remains blank until there is data present.
If I cut data from row 6 and paste it to row 17, the formula cahnges itself to:
[Code] ........
How I can lock down this formula so that the ranges remain the same i.e. $AT$6:$AT$26, despite changes on the source sheet? I have tried F4 to toggle relative and absolute references and this has made no difference.
View 4 Replies
View Related
Aug 28, 2012
Basically where the columns say 2011 or 2012 AND 1, 2, 3. I want to be able to have it index the number below based on the GL number on the left and both the year and period on the top. I think that you can do with using the sumproduct function with the binary, but the computer is a little dated and it takes a while to run those calculations.
2011
2011
2011
2012
2012
2012
[code].....
View 5 Replies
View Related
Sep 25, 2009
I've 2 worksheets, Summary and Sales. I need to get the total sales figure from the "Sales" worksheet onto the Summary worksheet. Sales data are pasted onto the worksheet monthly. Tried using vlookup but problem is, the column containing the total sales figure may vary and I need to update the vlookup formula. Another problem is that sometimes the Total sales (the lookup value) is not in the first column. Tried using the Index and Match combo but cannot get it to work. I've attached a simple mock up to hopefully illustrate my case better. I believe the experts in this forum should find this rather easy!
View 14 Replies
View Related
Jun 7, 2014
I am trying to use a nested INDEX and MATCH array formula to return the value in column C when matching column A and column B, but with a few more criteria.
The range containing all the data
A
B
C
1
Cat 1
January 1, 2014
John
[Code] ..........
I am looking for the array formula to return the name of the person in column C who is in Cat 1 after the date in column B.
For example;
C7 should return "John" because B7 requests "January 15, 2014", which is after the value in B1
C8 should return "John" because B8 requests "February 15, 2014", which is after the value in B1
C9 should return "Andrew" because B9 requests "August 15, 2014", which is after the value in B4
The best try I had for the formula in C7 was
{(INDEX($A$1:$C$4,MATCH(1,($B$1:$B$4>=B7)*(A$1:$A$4=A7),0),3))}
This brings back "John" as desired in C7, but when copying down the table into C8 and C9 both C8 and C9 return Andrew.
I guess this is due to my ">=" condition in the Match formula and it is returning "Andrew" because "Andrew" is also after the date requested, but I cannot for the life of me work out how to get it to work.
View 2 Replies
View Related
Aug 26, 2013
I just tried installing a basic macro which reads from a shared file on the office server onto my colleagues computers. Works find on mine but doesn't work on theirs "Could not set the list property index". When I comment out the offending line, the form loads just fine but obviously without the listbox populated. The line is:
Code:
.popsLB.List = Application.WorksheetFunction.Transpose(camp_array)
All machines are running Office 2007.
View 2 Replies
View Related
Oct 8, 2009
I have this table
.......A.....B.....C....D
1.....I......a.....d.....g
2.....II.....b.....e.....h
3.....III....c.....f......i
As you can see, the number I has a,d,and g, II has b,e,and h, and III has c, f, and i
I want to make formula that if I make the input g it would return I, f would return III, and c would return III, and so on
I want to make four formulas by using VLOOKUP, INDEX, MATCH, INDEX&MATCH separately.
View 9 Replies
View Related
May 1, 2014
If I have a column of data with a bunch of values (which can't be sorted & which is constantly changing so cant be broken into another column and then sorted) what formula do I need to output the second occurrence of a value in that column?
View 3 Replies
View Related
Aug 27, 2009
I have used this technique before with a sumif formula, to use the column that matches a reference cell but I cannot get this to work on a sumifs formula. What I have used previously in a sumif formula (only 1 criteria) is below.
View 3 Replies
View Related
Dec 20, 2012
How to get the following index/match formula to work.
I have 2 criteria that I need to find the result of in a table that has the one criteria down the column, the other across a row at the top.
The formula I have is thus far:
{=INDEX($K$46:$AV$46,MATCH(L98,IF($K$15:$AV$15=N98,$K$27:$K$82),0))}
In this L98 is the criteria I'm in theory doing a vlookup against the values in column $K$27:$K$82, and N98 is the theoretical hlookup against row $K$15:$AV$15, bringing back the intersection point from row $K$46:$AV$46
All I get is #N/A .....
View 5 Replies
View Related
Jul 22, 2013
Basically, I want a code that does a simple index-match function for a column - But for all the #N/A's that come up in that column, I'd like it go to another INDEX-MATCH function - and another after that. I'm trying to keep it all in one column.
Here is an example of the first index match
=INDEX([FILENAME.XLSX]DealerDetails!$A$2:$A$15000,MATCH(L2,[FILENAME.XLSX]DealerDetails!$CJ$2:$CJ$15000,0))
If this turns out an #N/A, I'd like those #N/A's to switch to this formula:
=INDEX([FILENAME.XLSX]DealerDetails!$A$2:$A$15000,MATCH(M2,[FILENAME.XLSX]DealerDetails!$CK$2:$CK$15000,0))
Then the remaining #N/A's to go to this one:
=INDEX([FILENAME.XLSX]DealerDetails!$A$2:$A$15000,MATCH(G2,[FILENAME.XLSX]DealerDetails!$G$2:$G$15000,0))
The column I'm referring to will have around 2,000 records or less.
View 1 Replies
View Related
Jul 18, 2014
I have a victim of the Index-Match duplication problem in Excel (2010). Basically, I have three columns of data, all daily input for the year.
Column 1 = Date
Column 2 = Actual (Units Sold)
Column 3 = Scheduled (Units Sold)
The Date is filled out through the end of the year as is the Scheduled values.
The Actual values are filled out daily.
I need to generate a summary box that reports Actual, Scheduled, and Variance (Actual - Scheduled) for the time periods Daily, Month to Date, and YTD.
My problem is that when I try to return the Schedule value that corresponds with the date of the last entry, I don't know if I am pulling the correct Schedule value since I do not know if the Actual value (that is pulled from the last value in the Actual column) is unique. So I tried using an Index-Match formula to return the latest value (that is the last record occurrence of the value) to my function in order to retrieve the correct Schedule value, but, sadly, it did not work.
I by no means am an Excel expert like many of you, so I may have some questions along the way.
I've attached a sample extraction from my worksheet and included an example of the Summary panel I'm creating.
ActualVsScheduled_problem - Copy.xlsx
View 14 Replies
View Related
Sep 13, 2012
The context of this problem is that I am pulling sales data from different outlets to analyse monthly sales. The data from the various outlets are in various formats and the products, even though they are the same throughout all the stores, are named differently. For example, Mushroom Soup can be named as "Soup Mushroom", "Mushroom Soup" or "Cream of Mushroom". This makes analysis really tough.
I first created a table with a list (K6:P6) that had the varying names of the products. This is shown as the first table with 3 rows.
Then I tried to match this list to a range of products sold by an outlet e.g. Electric. (shown in the next bigger table $D$10:$D$254, ) Next, I used Index to call up and display the sales of that product next to table with list A ($G$10:$G$254)
This works for some entries but not for all. I get mostly #NA answers even though I do ctrl-shift-enter. Also, the bigger table is in a separate file and I am using excel 2010.
=INDEX('[GON-JUN 2012.xls]gon-jun'!$G$10:$G$254,MATCH(K6:P6,'[GON-JUN 2012.xls]gon-jun'!$D$10:$D$254,0))
[URL]
View 8 Replies
View Related
Dec 24, 2013
I am currently using the below formula to add values (D6:D225 on sheets Mon, Tue, ...) given the criteria is met in any cells within the two columns across the five sheets (E6:E225 & N6:N225 on sheets Mon, Tue, ...).
When the references to the columns do not randomly change themselves to #REF! it works fine however, it does this often, forcing my to rewrite the formula.
**Why does it do that and is there a way to stop it?
Also, when I do have to rewrite I am forced to do each cell individually to change the number within the quotes. I have tried to use a cell reference there but when I do Excel just gives me a "0" as if there is no data to be retrieved.
I am using Excel 2010 on a company computer with the macros blocked so no VBA.
Code:
=SUM(IFERROR(INDEX(Mon!$D$6:$D$225,MATCH("2000",Mon!$E$6:$E$225,0)),0),
IFERROR(INDEX(Mon!$D$6:$D$225,MATCH("2000",Mon!$N$6:$N$225,0)),0),
IFERROR(INDEX(Tue!$D$6:$D$225,MATCH("2000",Tue!$E$6:$E$225,0)),0),
IFERROR(INDEX(Tue!$D$6:$D$225,MATCH("2000",Tue!$N$6:$N$225,0)),0),
IFERROR(INDEX(Wed!$D$6:$D$225,MATCH("2000",Wed!$E$6:$E$225,0)),0),
IFERROR(INDEX(Wed!$D$6:$D$225,MATCH("2000",Wed!$N$6:$N$225,0)),0),
Excel 2010
View 9 Replies
View Related
Mar 7, 2013
I am having a problem updating a jpg file in an existing Excel file which I created. There is a LOGO in the right hand side Header block which I can delete ( the words &(Picture) appear after the logo is deleted. For some reaon I cannot replace this LOGO with a newer version. The new LOGO appears in the body text area, bot it will not drag into the header. I must have missed something very basic!!
View 4 Replies
View Related
Nov 23, 2011
I am using Excel 2007 and I would like to move one tab to right with "Ctrl+Tab" and move one tab to the left with "Ctrl+Shift+Tab".
Macros will not let me assign "Ctrl+Shift" keys (that I could see). How can I accomplish this?
I am trying to use these exact keys because my mouse is setup so that leaning the scroll wheel left or right moves left or right in my tabbed applications by executing a keyboard shortcut. This works in all my applications except excel where the shortcut keys are different.
View 6 Replies
View Related
Feb 22, 2013
I have two data points a Talk Time Value lets say 2:08 and the number of calls lets say 10 and need to pull the column heading value for the number of agents needed lets say 2. My "Grid" is saved in one sheet and the my variable data values are in another. I need to somehow pull the closet match of my talk time value 2:08 and the calls value 10 to match up on my grid and give me the Column Heading which is the number of agents. Which in this example would be 2 agents. Just for reference in my other sheet I have my Talk time Value in C3 and my Call number value in D3. I tried various Index and Match formulas but have not got the one that I need to work the closet I have is =INDEX(Sheet2'B1:D1,MATCH(D3,INDEX(Sheet2'B2:D7,MATCH(C3,'Sheet2'A2:A7,1),0))).
example: in Sheet 2
ColA Col B Col C Col D Sheet 1 C3= 2:08 D3=10
Row 1 1 2 3
Row 2 1:00 0 3 4
Row 3 1:15 1 5 6
Row 4 1:30 2 7 8
Row 5 1:45 3 9 10
Row 6 2:00 4 10 11
Row 7 2:15 5 12 13
View 3 Replies
View Related
Mar 13, 2013
I'm using Index/Match to find a value in another workbook, but there is one value that I want to ignore. For example here is my function --
=INDEX(ZZANALYSIS_PATTERN.xls!$A:$H,MATCH(D8,ZZANALYSIS_PATTERN.xls!$G:$G,0),2)
In D8, the value is Blue. Here is an example with made-up values (X is meaningless values) --
A B C D E F G
X Apple X X X X Blue
X Pie X X X X Blue
When I use the function, I want it to retrieve Pie, but it always retrieves Apple. Is there a way to ignore Apple? I've tried throwing "Apple" in there a couple different ways, but nothing is working. Or, if I can search from the bottom up, that would work as well.
View 9 Replies
View Related
Jan 7, 2013
Using Excel 2010.
Rows 1 and 2 (range: A1:CM2) within Spreadsheet X contain the range of data that I need.
Within Spreadsheet Y, Row 6 will change monthly and will be input by the user. The value in Row 6 equals the data in Row 2 in worksheet X.
I need to put a formula in row 4 of spreadsheet Y that returns the value of Row 1 in Spreadsheet X.
Unfortunately, I cannot change the spreadsheet layout, otherwise I could do an HLOOKUP formula using Spreadsheet Y Row 6 and Spreadsheet X Row 2 and be done with it. I assume that I need to use an Index-Match formula, which I am not as familiar with. In addition, all of the online support I have found explains Index-Match with data in columns, not rows.
View 1 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
Jan 30, 2013
I am using Excel 2007 and importing a list of coordinates. The coordinates are in a text file and separated by commas, so I'm opening the file as a delimitted file. All of the coordinates import correctly except one. The value is supposed to 52530.6372, but Excel is automatically changing it to 52530.6371999999
If I manually type a "2" to replace the "1999999", it changes it back. If I type "3" it stays as a 3.
I added a round function to force it to round to 4 decimal places and that seemed to work until I then tried using the number with text commands. When I used an ampersand to insert the number into a text string, the additional decimal places are back, even with a round function!
View 1 Replies
View Related
May 9, 2013
So, We are using Excel 2002 & Excel 2007 and for some reason all of the workbooks that we have open decide to randomly change the fill colours of nearly all the cells. The only reason this is an issue is that a lot of my work envolves me sending reports to clients and from a business perspective this does not look professional at all.
To add to this, once the cells have changed colour, the original colour is no longer available to select.
View 3 Replies
View Related
