Vlookup Search Of Multiple Columns
Feb 23, 2007
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?
View 3 Replies
ADVERTISEMENT
Oct 6, 2008
Would like to be able to use a vlookup formula that incorporates multiple search criteria. In C10, I have a specific rank listed. In C11, I have the supervisor listed. The formula needs to list the name of the agent in C12 based on the criteria in C10 and C11.
agent rank score supervisor
A B C D
1 leo 1 99 jim
2 bob 3 68 jim
3 sam 1 73 ann
4 tim 2 84 adam
5 alf 2 55 ann
6 dan 3 42 adam
7 will 2 79 jim
8 sara 1 91 adam
9
10 Rank: 1
11 Supervisor: adam
12 Agent: " "
View 2 Replies
View Related
Feb 7, 2014
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.
View 6 Replies
View Related
Jul 28, 2012
The first tab of the excel sheet called 'Data' displays information on the years that 2 countries traded with one another. For example, from the years 2005-2010, Belgium traded with multiple countries (Australia, Brazil, Canada, Chile). However, there could have been one year or multiple years where it did not trade with a particular country (e.g. in 2006, it may have skipped trade with Chile).
What I'd like to do is basically create a function that searches through the data and returns a 1 if Belgium traded with a particular country each year from 2005-2010 and a 0 if it did not. So, for instance, because Belgium traded with Australia, Brazil, and Canada in all 6 years, the function would return a 1. But, because it did not trade with Chile in all 6 years, it will get a 0.
The second tab called 'Results' displays what I'd like it to show. I've tried using if statements (but they don't work with text), vlookups, match, index .
View 2 Replies
View Related
Apr 5, 2014
I need to find the fruit name in the table, and then reference the color from the corresponding row's Column G value. I have experimented Match, Index, Sumproduct, with no luck. Do I need to somehow nest with a VLookup?
Range A1:F4 Fruit Color (Col G)
VeggieVeggieApple VeggieVeggieVeggie Red
ClementineVeggieVeggieVeggieVeggieVeggie Orange
VeggieVeggieVeggieVeggieBananaVeggie Yellow
VeggieVeggiePear VeggieVeggieVeggie Green
Name Color From Col G
Apple Use formula to pull "Red"
Clementine
Banana
Pear
View 4 Replies
View Related
Nov 26, 2006
=INDEX('Cost Table'!$A$4:$C$305, MATCH(A1308,'Cost Table'!$C$4:$C$305,), MATCH("Item Description",'Cost Table'!$A$4:$C$4,))
I have the above formula to search a worksheet for an item description. It looks for a part number in one column. I need to now search two adjacent columns (maybe more down the road), can I adjust this to do that?
View 9 Replies
View Related
Oct 16, 2012
show me a formula using the criterias under Column E so that cell F2 will return the result as "202 IR" and cell F3 will return the result as "654 CC"? the data is saved over three different columns
A
B
C
D
E
F
Class1
Class2
Class 3
Staff #
Staff & Class
125 CC
758CC
1214 CC
202
123 IR
213 CC
2314 IR
654
456 IR
202 IR
654 CC
View 8 Replies
View Related
Apr 9, 2008
I am looking to write a search function that searches through a whole document. The only examples I can find are of a could different search functions but they all are searching an specified individual column and even then I didn't really understand them. My worksheet has 6 different columns which are all different labels for a certain tool. I want the user to be able to input any of those 6 labels and have all the information in that row be returned.
View 4 Replies
View Related
Feb 21, 2013
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]....
View 1 Replies
View Related
Jun 20, 2007
I have text (eg. 1-* or 10-* or 11-*) being entered in the following columns
Q21:Q128
R21:R128
V21:V128
W21:W128
AA21:AA128
AB21:AB128
AF21:AF128
AG21:AG128
AK21:AK128
AL21:AL128
AP21:AP128
AQ21:AQ128
AU21:AU128
AV21:AV128
AZ21:AZ128
BA21:BA128
BE21:BE128
BF21:BF128
BJ21:BJ128
BK21:BK128
I would like Excel to search the above columns provide the cell location (eg. 1-10-*) is located in cell BF26
View 9 Replies
View Related
Jun 10, 2014
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
View 14 Replies
View Related
Dec 16, 2012
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?
View 2 Replies
View Related
Jul 20, 2011
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?
View 5 Replies
View Related
Aug 19, 2013
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.
View 2 Replies
View Related
Sep 27, 2011
Rep Name is Column J Row 5. I need to find multiple columns based by the header which is row 5, if column range (j5:az5) is "Video - Actual" then copy column to sheet "Data", this needs to be repeated for about 8 more times for different criteria
Rep NameBundle - TargetBundle - ActualVideo - TargetVideo - ActualJoe, Jane
2.06.06.04.0Smith, John
7.010.02.06.0Tucker, Chris
19.039.05.017.0Sandler, Adam
15.011.06.010.0Iglesias, Gabriel
8.03.06.010.5Smith, Will
8.03.06.06.5Powers, Austin
10.025.07.09.0
View 1 Replies
View Related
Sep 21, 2013
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.
View 9 Replies
View Related
Dec 19, 2008
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?
View 4 Replies
View Related
Sep 14, 2009
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.
View 9 Replies
View Related
Sep 11, 2012
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 Replies
View Related
Oct 27, 2013
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.
View 2 Replies
View Related
Apr 18, 2014
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
View 9 Replies
View Related
May 8, 2014
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 Related
Jun 27, 2011
I'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
View 4 Replies
View Related
Mar 30, 2009
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.
View 4 Replies
View Related
Feb 20, 2012
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
View 7 Replies
View Related
Jun 12, 2014
My Excel program (Excel 2010) currently has several columns and each column looks for and pulls data from a specific file on my computer. Then I need to delete any duplicate data entries, count the number of unique entries and track the changes through a chart. I have everything done except I cannot figure out (or find on the internet) a way to search in multiple columns (more than 2) and delete just the duplicate cells. I want to delete the cells in a way where there is one left. For example if the code 12gf is duplicated three time, I want to be left with one 12gf (it doesnt matter what column the original one is left in). Additionally, column length changes and they are not sorted. I have attempted to attach an image of an example file below.
View 14 Replies
View Related
Jul 15, 2012
What i have so far is:
-3 Sheets (Sheet1 - Search, Sheet2 - Data, Sheet3 - Result)
-Sheet1 has a userform ready to link up - this opens up when workbook and worksheet is activated.
-Sheet2 has some example data to search - the search is for column 1 (see attachment)
-Sheet3 has a template ready for the data to be pasted to.
I would like the search result is column 1 to copy the data from columns 1 to 7 to sheet3.
I want the template/layout to remain after the pasting of results. (everything protected from editing, if possible).
Once finished (ie go back to sheet1, i want the data from sheet3 to be cleared.)
View 9 Replies
View Related
Jan 15, 2010
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 Related
Feb 10, 2010
I 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 Related
Oct 11, 2011
Im am trying to create a search marco button that allows me to search in multiple worksheets in one work book. I came across this CODE the first part of it works. It pops open user input box and ask for the word that i would like to search but the this error message pops up Runtime error1004 Method 'range" of object'_Global'failed and i dont know what to do
Private Sub SearchButton_Click()
SearchString = InputBox("Enter Search String", "Search")
If SearchString = "" Then Exit Sub
For Each c In Range(myRange)
If InStr(LCase(CStr(c)), LCase(SearchString)) Then
[Code]....
View 1 Replies
View Related
