Vlookup Or Similar With Variable Col Index Number Value
Dec 21, 2007
i am looking to create a vlookup but with the ability to easily change the column number index so that i can use different columns.
As an example. In a worksheet i have a table with the names of cars in column A starting row 3. Column B to m Row 1 is headed Jan to dec, row 2 same columns is a country name eg UK. Column N to Y row 1 is Jan to Dec again and row 2 of these columns is a diff country say Germany. This repeats for a few more countries. The data within Row 4 for these columns i.e per car is all prices. The table therefore shows the prices of cars per country per month.
I then have a seperate worksheet for each country where the cars are again listed in column A and Jan to Dec is in column b to M but the data is hard coded being the number of cars. I would like to use column N to link to 1 of these months hard coded counts dependant on what month i decide to forecast on. The easy way being that if i wanted to use Jan count number i would link the count for that car type to =b4 etc. Is there an easy way to allow me to change the link should i decide i want Feb ?
The second question is within each countries worksheet i want to bring into column p the countries related car price for a month i select. It may be that the count number differs from the price i select.
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.
From the highlighted cells at B17 to B19, while using some kind of lookup or indexing method, I want to count the number of cells in that specific range that's great than zero. This list is just a sample, the list will be much bigger and order may not be the same--that's why I need to lookup the project name.
I have created a sheet that contains a new diet program, calculated down to the precise calorie required for my training routine. Please see below for an example of one of my daily meals:
[url]
I need a formula that will help me to create a weekly shopping list (as the values in the example above will change on a regular basis). So I need to take all values from the from columns A & B, multiply them by 5 and then show me the totals in another sheet.
Similarly, I need to do the same with columns E & F, only they need to be multiplied by 2. The totals then need to be added to give me the required amount (in grams) for the week.
I have attached the file, in the last sheet (Consolidated) i need to get the data from all the previous sheets, my problem is that whenever i have similar cells the data that is showing is only the first value and not the sum of all values,
I would like to sum the values of a range using something similar to vlookup. The problem with using vlookup is that vlookup returns only the first instance. I have multiple values therefore vlookup would not work. I have 2 variables which is causing my frustration. I have added an example to explain. On the column where it says "correct answer" is where the formula should go. I displayed what the formula should produce so that it explains what I want as the output. The "static column" which contains variables 1 and 2 doesn't change.
The two variables it needs to search for is shown in the variable 1 and variable 2 columns. And the lookup range is on the left.
So basically it needs to match the two variables in the range and then grab the sum of ALL quantities where the two variables match . quantity amount is found on the leftmost column.
Is this possible. sumif works but only with 1 variable.
I have one sheet that contains data about products, the product number, the class it belongs to, and many other bits. The data is sorted by product number.
In a second sheet I have a list of classes in column G, I'd like to return a list of product numbers in columns H to wherever is needed.
For example there might be two products in class one, class one is in G1, with the first product number in H1 and the second in H2.
I am working on a customer report template that generates our customers reports and will send them out automatically.
This issue I have now is that the system that generates the raw data for these reports only lists the Customers name in a column with an entry for each line of data, the thing is though that the system has lots of variations of the customers name, even more so if that customers has different departments.
What I need to do is from this list of customer names, I need to automatically figure out what the "common" name is or main name so to speak, and then make a variable using the correct full name, which will be used later on in the code to import correct logos, and direct the reports to the correct people.
Here is a quick example of what data we get raw from the system:
Customer Name: John Build Johns Buildings Johns Ltd Building Johns Plumbing Department Glass Doors Ltd A Department of Johns Buildings Johns Building Corporation Hole In One Golf Range
This is just an example, we have thousands of clients, so the length, number of words etc can change alot. Ideally I from a list similar to that I would get a full proper result of "Johns Buildings Ltd" for example, this would then be in a variable to be used in code from then on to reference doing certain things with the reports of Johns Buildings Ltd.
You'll notice there is one name "Hole In One Golf Range" that seems to have no relation at all, this is correct, ideally I would also like to build in some error checking into the code, so that rows like that that have nothing to do with the others would get deleted.
So how would you amazing VBA gurus go about working with data like this? I'd prefer a more general answer with explainations that just straight code, as I'm sure I will have to adapt the hell out of it for it to be useful in context.
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.
I would like to find the value from Sheet2 Column1 in sheet1 Column1 and return value from Sheet1 Column2 and Column3 into Sheet2 Column2 and Column3
And if it doesn't find anything just return Not Found
The problem that a Vlookup is not working for me is because I want it to be the exact text from sheet2 column1 but in sheet1 column 1 it should not be exact as it might have some extra text as seen in the illustration above
I am trying to find some missing values compared to 6 base values. For instance, I have a sheet with some names translated to another language, I am trying to find the languages some names have not been translated too.
For example, if I have six languages, Arabic, Japanese, Russian, Chinese-Simplified, Chinese- Traditional, and Korean to compare too, I want to find any names that are not translated in certain languages.
Sample:
John Japanese John Chinese - Simplified John Korean Martin Arabic Martin Chinese - Simplified Martin Russian Ramon Arabic Ramon Russian Sam Arabic Sam Chinese- Traditional
this does not work, is there a possibility to do this? the index from where te values should be read is calculated in the macro, so i can not use fixed values.
On the attached Excel file, I have code that will insert a variable number of rows and copy and paste from and to variable positions. That all works fine when run from a command button, but when I try to run it from the Worksheet_Calculate by entering 1 in J1 or K1 (inrange cell is J1+K1 for testing purposes) the CommandButton1_Click sub runs continously until an error occurs.
I'm trying to perform an index / match lookup that can have a dynamic indexed column.
Sheet1 has the master table and sheet2 is where I'm putting the formula. The table on sheet1 has a RecordID field that uniquely identifies each record and that must be typed into Sheet2 [RecordID] column and then matched to sheet one to identify the record
Code: MATCH([RecordID],Table1[RecordID],0)
The column headers on Sheet1 are used as data validation for the column [Field] on sheet2. The value of the Field column on sheet two should determine the the column on sheet1 to index.
I would imagine that the title of this thread made little sense, so let me try to explain:
Sheet A has columns A-C populated with data. Column D needs to search Sheet B columns A-C for a match. (ie, see if the data on sheet A is also on sheet B) if this is true, then it needs to return the value of the LAST column of the matched row in Sheet B.
So, if Sheet A R1 A-C is 1,2,3. Sheet A R1 D needs to search Sheet B for 1,2,3. and when it finds it, (for example on row 9) return the value of the last column of Sheet B row 9. The issue is, the last value could be in column R or S or AA, there is just no way of knowing.
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.
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.
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.
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.
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.
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"?
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?
I would like to create a spreadsheet with 3 columns: employee ID, last name, first name. Then on the 4th column I would like to enter the employee ID and have the last name and first name auto populate into the 5th and 6th columns. How do I do this?
Attached example file has data with which I need to do the following;
On the tab "part list" I put in the part number in column F. Column C should then be populated automatically by looking up column F in the tab "Lookup" and showing the value found in column B of that tab.
The difficulty with this is, is that when the part number in column F starts with "DN", the lookup value should end with "FD/A" and when the part number in column F starts with "Z", the lookup value should end with "TY/A".
This last part I can't get it to work. I've tried vlookup with a index/match function inside, but it won't work somehow...
