Vlookup With Changing Column Index Numbers
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
ADVERTISEMENT
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
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
Jan 22, 2010
Can Vlookup use multiple column index. How can I solve my problem depicted in attached file? Is there any solution?
View 6 Replies
View Related
Apr 21, 2009
VLOOKUP($L4,'[mail.xls] new'!$A$6:$AB$261,6,FALSE)
Which gives me proper result...but the problem i m facing is i have many columns after L4 like M4, N4, O4 and so on....where i have to get the result.
So for that every time i have to paste the above formula and manually change the col_index_num i.e. 6 then 7,8,9 in every column where i use the above vlookup. So is there anyway that it will append the col_num_index by 1....i.e.
View 9 Replies
View Related
Apr 29, 2014
I want to merge two tables with keeping the data in the two tables in two worksheets, so my idea is to create a drop list in one of the two worksheets and depending on what the user will chose the datas will appear. for informations, the datas are all Vlookup in the same workzone but for each table the lookup will be done in differents columns, so how to make the column for the vlookup changement and depend on what I choose??
clarifications: it's like the choice on the droplist will be a link cell to the column of the research.
View 3 Replies
View Related
Oct 25, 2007
Is there a way to automatically change the column index number in the VLOOKUP formula when copying the formula to columns? For example, when I copy a VLOOKUP formula from column A to column B, the cell references will change, but the column index remains the same. I'd like the column index to be increased by 1.
View 2 Replies
View Related
Aug 10, 2012
How can I do the following....if for example I have the following vlookup;
Code:
=vlookup(A12, 'sheet 2 $A$2:$ID50$, 3, false
How can I change the column index i.e. the 3, to reference to a cell.
Code:
=vlookup(A12, 'sheet 2 $A$2:$ID50$, H1, false
The above example doesn't work but I'm sure something can be done using TEXT or VALUE
View 3 Replies
View Related
Apr 5, 2014
does it not like dates? i have columns of data and i wish to search it for each month and then print a months worth. here is part of the data. maybe vlookup does not like dates? i tried putting an index column on the left, 1,2,3,4... but it still would not pick up "Jun"
View 8 Replies
View Related
Dec 1, 2009
Is there a way to reference a cell value instead of using a static value for col_index_num? For example - Instead of telling it to look in the 5th column of Sheet2, I would like it to look in the column were the heading (row 2) in Sheet2 is equal to the value of O2 in Sheet1.
Here's another example -
Normal formula: VLOOKUP(a2,Sheet2,5,FALSE)
What I'd like: VLOOKUP(a2,Sheet2,column where row 2 equals the value in 'Sheet1'!O2, FALSE)
View 7 Replies
View Related
Jun 20, 2007
I am pasting vlookup formulas into a spreadsheet using a macro, and want to change the reference column number based on a variable generated within the code eg
= vlookup(RC1,table,i,false) where i is a predetermined variable in the code
for i=23, I need the result to be of the form
=vlookup(a1,table,23,false)
View 7 Replies
View Related
Jun 7, 2014
I am creating a spreadsheet to manage Block ID conflicts when adding mods to a Minecraft Server. Example spreadsheet is attached.
Description: Column A contains a list of available numbers (e.g. 1-20 and 31-50). Column B contains a list of numbers a new mod tries to use up by default (i.e. 1-25). A number in Column B that is not in Column A must be changed to the nearest number that is in Column A. In this example, numbers 21-25 in B are not available in A and must be changed to 31-35, respectively. Column C should be the result of changing Column B (i.e. 1-20 and 31-35).
Issue: The main problem is that as a given cell in Column B changes, that number needs to become unavailable in Column A for the next change. For example, when 21 in B returns 31, 31 can no longer be used for the next number in B (22).
I have been able to use VLOOKUP or INDEX/MATCH functions to lookup the numbers to be changed.
View 2 Replies
View Related
Jun 14, 2009
Is it possible for excel to monitor Row J for a letter "Y". If a "Y" is put into a cell in row J a macro runs. If the "Y" was in column 6 I need the macro to copy A6 B6 C6 D6 E6 F6 etc to other cells on a different worksheet.If "Y" was in column 7 it will copy A7 B7 C7 etc to the same other cells on a different worksheet.
View 14 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
Apr 29, 2009
I have a series of numbers in one column. Is there some way I can divide all the numbers together by, say 10, without doing it manually?
eg.
12
15
15
25
20
should become
1.2
1.5
1.5
2.5
2.0
Is there a way in Excel to do that?
View 3 Replies
View Related
Mar 3, 2014
The below piece of code carries out a vlookup on a defined cells value and produces a result in sheet one, however if the column index number in sheet 2 (Database) is empty the result 00/01/1900 is produced.
I'm not sure how to say leave the result blank if the column index number is blank.
Excel 2010
Userform = Tab 1
Database = Tab 2
View 6 Replies
View Related
Aug 27, 2012
I have a column (B) of randomly generated numbers 1-14, and am using this formula range to sort in descending order and return the relevant value from column (c).
=VLOOKUP(LARGE(B3:B20,1),B3:C20,2,) to =VLOOKUP(LARGE(B3:B20,14),B3:C20,2,)
Works great, except when a number is duplicated, (E.G. 14,13,13,12,11,10,10,9,8 etc). It then returns the first value from(C) repeated, and not the value from the second and subsequent duplicated reference numbers.
View 3 Replies
View Related
Jan 11, 2008
I have a long list of tabs listing "projects" which have changing names - on the first sheet, I want to have the table of contents automatically update and link to each tab - I want the user to only have to change the tab name to have the table of contents and link update -
View 5 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
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
Dec 13, 2013
I have a variety of different order forms in workbooks titled IT1314-0001, IT1314-0002, IT1314-0003...etc... They're all stored in the same location, but they need to be on different workbooks as each workbook is sent as an order form to a different organization, for that organization to fill in. All of the order forms have the same layout, but the user will input different information onto them.
In a summary workbook of all of these Order Forms, I need to be able to drag down an INDEX formula, such that the INDEX looks in a different workbook (the next workbook in the sequence) with each row. As you go down the rows, it should look like;
INDEX(‘[IT1314-0001.xlsPARTS A & B!$D$5:$E$5, 1, 1)
INDEX(‘[IT1314-0002.xlsPARTS A & B!$D$5:$E$5, 1, 1)
INDEX(‘[IT1314-0003.xlsPARTS A & B!$D$5:$E$5, 1, 1)
INDEX(‘[IT1314-0004.xlsPARTS A & B!$D$5:$E$5, 1, 1)
Can this be done without VBA?
View 7 Replies
View Related
Jan 13, 2009
I have tried the Vlookup formula, and also the index/match formules but I can't get my desired outcome. As you can see the setup is fairly simple. For turnover I want to lookup the values based on Period AND profit center This last bit is my problem, how to define a double criterium.
The idea is to choose the wanted latest estimate (cell B1) and that for turnover, discount etc the values are based on 2 criteria (periode and profit center)
I have also added the desired outcome to clarify my question.
View 5 Replies
View Related
Oct 22, 2009
I have a worksheet that has numerical data in A1:A22. In E1:E61 I have set values in numerical order and in F1:F61 I have a letter or letters that correspond to E1:61.
I am trying to write a formula in B1 that will look at the value in A1 and then match it up in E1:E61 and then return the corresponding letter in F1:F61.
e.g: in A1 i have an INDEX and MATCH formula
View 2 Replies
View Related
Jan 27, 2010
I tried with the VLookup, and HLookup and neither one worked for me. I have a reference table with all the data, and then input sheet where I want to bring this data by using one of the excel function. ie. When I enter office name on the top of the input page, I would like the Tax ID # in cell C3 to fill in using the excel function from the data on the reference tab. Same with cell C14 & C15. see attached for an example.
View 5 Replies
View Related
Apr 17, 2009
I think I need to combine functions to allow me to automate data entry from a table from another worksheet. If the Name entered in column A on Exp Breakdown matches a name in column A of Salary Breakdown I cannot seem to write a function to scan the table in Salary breakdown and lookup the percentage that person is paid from a specific program number. I have attached a sample worksheet to hopefully make it easier to understand.
View 3 Replies
View Related
Jul 6, 2012
I have data on two separate tabs. Data in columns A of both tabs potentially match. I am trying to identify and pull the value in column C of the second tab where the values in columns A match and the first instance where the value in column B on the second tab is less than the value in column B of the first tab. The values in both columns B are dates.
column A first tab = column A second tab
column B first tab < column B second tab (first instance of less than)
pull value from column C on second tab to appropriate row of column C on first tab.
View 1 Replies
View Related
Apr 3, 2008
I have two worksheets that I am hoping to use to generate one report in excel. Sheet1 contains the following information for my entire work center:
Benefitor Account Dollar Value Document #
Sheet2 contains a list of Benefitors that are relevant to only me.
I want to pull the information from Sheet1 where the benefitors on Sheet1 match the benefitors listed on Sheet2 into Sheet3.
View 11 Replies
View Related
Dec 4, 2008
I have a tab that has Employee Name in one column and the benefit they chose in another column. The employee name is repeated a number of times since they have chosen more than one benefit.
I am trying to create a new tab where the employee name in is the first column (only once) and each benefit is listed in separate columns along the top. I want to put an "x" in the column for the benefit the employee has chosen.
Is there a way to say "look for this employee, see if this benefit is what they chose, and if it is, put an "x", if not, leave blank"?
View 9 Replies
View Related
Jun 5, 2009
I am trying to get the info shown on table 1 into table 2. I suspect index and match is the answer, but I am not sure.
Table 1 Employee # Deduction Amount 101 AD&D 7.00 101 LIFE 4.00 101 LTD 12.00 101 STD 6.00 101 HEALTH 300.00 101 DENTAL 25.00 105 AD&D 8.00 105 LIFE 6.00 105 LTD 6.00 105 STD 300.00 105 HEALTH 25.00 105 DENTAL 8.00 112 AD&D 7.00 112 LIFE 5.00 112 LTD 13.00 112 STD 5.00 112 HEALTH 450.00 112 DENTAL 37.00 Table 2 AD&D LIFE LTD STD HEALTH DENTAL 101 105 112
View 9 Replies
View Related
Nov 30, 2008
I have the below formula....
View 6 Replies
View Related
