i have the following table, i want my Vlookup not only get the description from Column K but to get column K , L , M City and Country as well and put them in Columns D for Description, E for City and F for Country. would this be possible with Vlookup formula or any other formula?
View 3 Repliesin a vba module I currently have this
View 2 Replies View RelatedI have a worksheet which is used by multiple users at the same time, at the end of the day I can have upto 10 files. The layout of all the files is the same, same number of columns and rows, the only difference would be the file name eg:Test1.xls, Test2.xls etc. information in test1.xls will not be any other file. At the end of the day, these files are emailed and saved in a particular folder.
What I need is get information from all the files saved in a particular folder and keep columns which have data. Additionally, if there is no data for any columns after combining the columns to display an alert. The final file should have information with only data in individual columns and arranged in the order such as Center 1, center 2, Center 3
Eg:test1
Column A Column B Column C.....upto 30 columns
Center 1 Center 2 center 3....upto 30 centers
1 4
2
3 5
test2.xls
Column A Column B Column C.....upto 30 columns
Center 1 Center 2 Center 3.....upto 30 centers
5
The final file will look like this
Finaltest.xls
Column A Column B Column C.....upto 30 columns
1 4 4
2 5
3 5
I have quite a long sequence of column for which the headers are all two-lines titles (with a line break). I need to copy many columns that are discontinuous, and the operation would save me a lot of time if I could select them at once. Of course, I know about selecting the columns holding CTRL, but here is the snag: as soon as a two-line title column is selected, the formula bar displays the content of the header, obviously on two lines, and I can't select any more columns. I tried many things, but nothing could solve this...
Is there anything I can do besides copying the columns one by one (which would take a long time) or renaming them?
I have a spreadsheet with two date/time columns 'Date1' & 'Date2'. Each date/time column has its own column with corresponding values ('Var1' & 'Var2'). These dates cover the same time period, however values for 'Var2' were collected less frequently than 'Var1'. I want ONLY the values in 'Var1' that correspond to the dates in 'Date2'
I am trying to select values from one column 'Var1' which have correlating date/time in column 'Date1' that match the dates specified in 'Date2'. Basically I need the values from 'Var1' that match the same date/time as 'Var2'
See the attached image to make it clearer..
Excel2010
Excelhelp.jpg
I would like to loop through cells row wise.
Columns which have data are A:E
modify below code to select the range from columns A to E?
[Code] ........
Using VBA, I need to Select A1:C14.
The problem is that A1:C14 contains blank cells, and there is also an adjacent column D that I do not want to copy.
So, UsedRegion and CurrentRegion aren't doing it for me. (It selects Column D too.)
Obviously, this is an example...the real data set is an export and varies in size.
Certification and Training tracking.xlsx
I want to create a certification only list on a separate tab of training that has been completed where a certification has been issued (as indicated by a "Y" in the "Certification?" column on the training tracking tab) and then populate from some of the fields vs. all of the fields.
What I have now, only pulls the first occurence, not all occurences. I saw that I could have identified the multiple columns that needed to be populated, but it didn't work either, so I'm fine putting a separate vlookup in each column.
Im trying to make a vlookup so I can say find a record where column A is >4, B is =2, C is 1.
Example - The lookup would find these 2 ranges.
7
2
3
2
6
4
[code]....
I've used VLOOKUP based on matching one column and returning one result. No problem. Now I need to return one result based on 3 columns matching.
Assume this range of columns is named: TravelDays
Origin...Location...ShipVia...TravelDays
33778...420...UPS...3
33778...425...UPS...1
33778...440...UPS...3
33778...443...UPS...3
33778...446...UPS...2
33778...447...UPS...3
33778...449...UPS...5
33778...475...UPS...3
What I'm trying to accomplish is if these columns match:
i2=Lookup!TravelDays,1
E2=Lookup!TravelDays,2
N2=Lookup!TravelDays,3
My result should be: Lookup!TravelDays,4
I have a list of names I need to look up against another list. My problem is that the second list has a mixed formatting of Firstname Lastname and Lastname Firstname. What I did so far is create a second column that reverses the first and last names in the column.
Table1:
A
B
1
Name
Name (reversed)
2
John Smith
Smith John
3
Jackson Laura
Laura Jackson
4
Maxwell Ted
Ted Maxwell
5
Chris Hansen
Hansen Chris
Table2:
A
B
1
Name
Currently employed?
2
John Smith
=IF(ISNA(VLOOKUP([A1; Table1!A:A;1;FALSE));"NO"; "YES")
3
Laura Jackson
4
Ted Maxwell
5
Chris Hansen
The VLOOKUP formula works great but I need it to look for a matching name across Table1:A:B not just Table1:A:A. How would I do this?
i am trying to get a VLOOKUP to work over a number of different columns. An example the problem:
Sheet1
A B
1 10
2 20
3 30
4 40
5 50
Sheet1 is the sheet i want to lookup the values and i want to place the results into column B
Sheet2
A B C
1 10 40 50
2 20
3 30
Sheet2 contains all the infromation that i want to look for to make the comparison.
I know that the expression used for the VLOOKUP is:
Code:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
The issue i have is "col_index_num" as i want to look over multipule columns. I have been using this expression:
Code:
=VLOOKUP(A2,Sheet2!$A$1:$J$20000,1,FALSE)
How do i change the "col_index_num" to look over all 3 columns and not just the 1st, 2nd, or 3rd?
Basically, I have an array of data (let's say A2:D20). I then want to do a VLOOKUP across the columns A:D, and return the corresponding value in column G, if VLOOKUP can find a particular value from column F, say.
So, I need a VLOOKUP function written in G2, that finds the value E2 in EITHER A2,B2,C2 or D2 (and return what is in E2, say).
I first thought I could CONCATENATE the strings in A2, B2, C2 and D2 and use some sort of VLOOKUP that finds a string within another string.
I have two separate worksheets, and I am trying to create a Vlookup or Index and Match formula. Here is the example:
Sheet 1
Cell A1= Employee ID: 123-D.
Sheet2
Vlookup A1 from Sheet 1, and match the first five characters to Column A, Column I and Column P. If a match, return name (e.g. John Doe) in Sheet 1, cell B1.
I have a spreadsheet with twenty columns. Column A has an item number (say "Clutch"), and the remainder of the columns have values. However, there only be one column in the range B:T which will have a value on the same row as "Clutch" (say "Black" in column "N").
How I can I return "Black" using a vlookup or should I be using something else?
I have two columns of data each with repeats. I want to be able to look up the third column of data and then list the multiple results attaching to the first two columns.
I have attached a dummy spreadsheet of what I am trying to achieve.
creating an equation that could essentially vlookup off multiple columns.
I get a report once a week with a list of employee ID numbers. I have a separate spreadsheet with employee names, ID 1, ID 2, ID 3, ID 4, in columns A,B,C,D,E , respectively.
I am trying to put a forumla in the report where it will take the employee ID number and reference it to my separate spreadsheet and give me back a name if it matches any of the 4 IDs that pertain to that employee.
I've tried index/match but I believe match only looks at one column.
I have an XLS sheet filled with every zip code in the US... the zips are in multiple columns adjacent to columns with the corresponding county names.
Unfortunately, the there are up to EIGHTEEN columns of zip codes in each row describing the counties... not one zip per row. I'm assuming this was done to save space.
In another sheet, I have list of client zip codes and need to VLOOKUP the corresponding county from the first sheet. I'm certain I'm not using VLOOKUP right...I'm getting #N/A every time.
Is this because it's only checking the first column of zip codes? I need it to check all 18 columns of course... there are no repeated zips.
How do I do this without rebuilding the full zip database page?
I have attached a file with an example of what i am looking for. The data in columns A and B are my original Data. There are multiple instances of e-mail address on the left but I need to bring in each data horizontally. I would like a formula to enter into E2:J6 to pull the information in.
Book2.xlsx
Say in L4 on form test i want to look for the value of A4 in Drill data range a2 to z1000 and retun a concatenated string of something like "sav 407 : 08-may to 11-may"
View 3 Replies View RelatedI'm looking to simply find a function that allows me to lookup a certain value in a table of data and return the value in a specific column. So if I looked up the current date, 6/27/2011, it would return the week it is in (26). I dont want to be limited to just the monday column however, as the dates I will be throughout the entire week. Is there an easy way to do this in excel 2007?
MondayTuesdayWednesday ThursdayFridaySaturdayWeek
5/2/20115/3/20115/4/20115/5/20115/6/20115/7/201118
5/9/20115/10/20115/11/20115/12/20115/13/20115/14/201119
5/16/20115/17/20115/18/20115/19/20115/20/20115/21/201120
5/23/20115/24/20115/25/20115/26/20115/27/20115/28/201121
5/30/20115/31/20116/1/20116/2/20116/3/20116/4/201122
6/6/20116/7/20116/8/20116/9/20116/10/20116/11/201123
6/13/20116/14/20116/15/20116/16/20116/17/20116/18/201124
6/20/20116/21/20116/22/20116/23/20116/24/20116/25/201125
6/27/20116/28/20116/29/20116/30/20117/1/20117/2/201126
7/4/20117/5/20117/6/20117/7/20117/8/20117/9/201127
7/11/20117/12/20117/13/20117/14/20117/15/20117/16/201128
7/18/20117/19/20117/20/20117/21/20117/22/20117/23/201129
7/25/20117/26/20117/27/20117/28/20117/29/20117/30/201130
8/1/20118/2/20118/3/20118/4/20118/5/20118/6/201131
8/8/20118/9/20118/10/20118/11/20118/12/20118/13/201132
8/15/20118/16/20118/17/20118/18/20118/19/20118/20/201133
8/22/20118/23/20118/24/20118/25/20118/26/20118/27/201134
8/29/20118/30/20118/31/20119/1/20119/2/20119/3/201135
9/5/20119/6/20119/7/20119/8/20119/9/20119/10/201136
9/12/20119/13/20119/14/20119/15/20119/16/20119/17/201137
What formula should I use so that it will find the match with a 6-column table (with every other column containing the 'reference' items, so I can't use vlookup)
So that it will return the value next to the match
(instead of returning gibberish, will return a number instead)
Solved
Okay had that table setup that way because I needed separate tables ("linked" comboboxes")
So I just altered it so that the vlookup range changes dependent on the value of combobox 1.
I am using Windows xp and Excel 2007. I'm creating a user form and have a vlookup formula that goes to a table and looks up calories and sodium based on the text entered by the user. My problem is that I want to be able to populate the 3 columns with information and then sum the values that are returned to the Calories and Sodium columns. Is that possible with Vlookup?
=IF(ISNA(VLOOKUP(F8&G8&H8,Food!A:E,4,FALSE)),0,VLOOKUP(F8&G8&H8,Food!A:E,4,FALSE))
Water 24 ozOther DrinksGelsSolid FoodCaloriesSodium
00
00
00
I have created a spreadsheet to show some reports and I wanted to serch for some datas which overloops themeselves. If you can have a look at a test file I attached you will see the full picture. I have 2 tables, where the 2nd one is on the right side of the 1st one. 1st table:..............
View 3 Replies View RelatedI need to run a vlookup to find some data. But I have a lot of data about 600,000 lines. Currently this list is spread over several columns (as the limit is something like 50000). How can I make my reference range access multiple columns?
View 5 Replies View RelatedI am having trouble with a number of workbooks when using vlookup (using the wizard rather than entering the formula directly)
First I select the cell with lookup value, then try to select the table array on the source sheet but the cursor changes to the hand symbol and I can't click on any cells. I have had this problem with many different workbooks. The only solution I have found is to copy the whole sheet, paste-special-values into a new sheet and use that as source.
See attachment for full explanation of problems. I'm trying to get the correct sales tax when selecting a particular county. I can't get the vlookup to work and also I can't get the formula in the data validation of the county to work.
View 5 Replies View RelatedI have a table, that while writing a vba code I need to select the first column in the table (A) and the last column with data inj the table ( unknown which column it is). The last column might change (meaning that it sometimes column B, somestimes column Y etc...depends on the data). I need a way to select only the the first AND the last column in that table(actually it's the last column with data on that sheet). I'm sure there is must be a way I just don't know it!
View 2 Replies View RelatedI have a worksheet that currently has columns AB to XFB hidden. I want to unhide them, so I selected column AA and then drug the mouse over to the right (toward the empty space), intending to right-click and select "unhide". I do this all the time with no problems.
However, from the instant of my drag to the right, Excel has become totally unresponsive. I see it using 25% of my quad CPUs in task manager, but there is no active memory delta and it has been sitting for over 20 minutes now.
I'd rather not lose the work I just entered, but I can't seem to get Excel to come back to life by pressing escape or clicking in the worksheet. I've noticed slowdowns before when there is a lot of actual data in the cells selected, but these columns were empty to the best of my knowledge.
I am looking for a way to select specific columns in excel that have specific characteristics in the header titles. For example, I have a very large worksheet with columns that specify the year and month (ie. "1900-8"). The years range from 1900 to 2011 and each year has "-1" to "-12" to represent the months of the year. Specifically, I am wanting to pick out all of the columns ending in "-8" (August).
View 14 Replies View Related