Lookup/Search Multiple Columns/Criteria
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
ADVERTISEMENT
Oct 25, 2007
I am trying to run a lookup on a rather large table.
Column A Column B Column C Column D Column E Column F
Postal code City Province Postal code City Province
I am trying to look up the City and Province based on the Postal code and can't figure out how to do this.
There are too many Postal codes to fit them all in Column A, I have tried V Lookup, Index Match and can't get it to work.
View 9 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
Jun 17, 2014
I would like to create a formula in a summary sheet ("sheet 2 section" in attached)that looks up and sum cells based on multiple criteria in row and columns in "sheet 1 section". I thought I sumifs would work, but I kept getting #value errors. I'm not a power user in excel. I attached the spreadsheet - it is only an example of what I want to do as the real data is confidential and large. The result I should I get is in section 2. Lookup account 12.251 for tim in the month of February - result is 14.
Test2014.xlsx
View 3 Replies
View Related
Nov 2, 2009
I have a workbook with many many sheets in it. The first sheet contains a single column with about 10,000 different values. I'd like to use each of these as search criteria against ALL data in the other sheets (of which there are a good 50 or so). If matches are found (they don't have to be exact case), then I'd like two things to happen:
1. The rows containing the matched search criteria in the first sheet are highlighted.
2. In the cells adjacent to the search criteria in the first sheet, hyperlinks to the matched data are created and named after the sheet upon which this matched data appears.
I've attached a sample file to this post with ideal sample 'answers' to queries made of the first 2 terms.
View 3 Replies
View Related
Jan 29, 2013
I've got a sheet where I've got products in one column.
The second criteria is "week #" for which the row remains consistent but the column varies e.g. "week 8" could be in cell L12 today and in L18 tomorrow.
I would like to return the value of the cell at which a particular product and week intersect. e.g. if "product a" is in B20 and "week 8" is in L18, I want the value of cell "L20" returned.
Product to determine Row #, Week to determine Column # for returning value.
If they interest multiple times, I would like to sum up the values intersecting.
Neither product nor week # is unique in the sheet.
View 5 Replies
View Related
Apr 7, 2014
I have a table of data (say Column1 to Column 5) with multiple rows.
Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.
I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.
It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.
View 3 Replies
View Related
Oct 21, 2006
I am trying to solve a problem. I am currently using this formula
= SUMPRODUCT(--(Sheet2!B2:Sheet2!B300="MARKETING"),--(Sheet2!D2:Sheet2!D300="200612"),Sheet2!E2:Sheet2!E300)
This formula works for me as it is but I would like to add more months to 200612. I want this to also be 200701 and 200702. In another cell there will be up to 10 months. Is there a way to do a Vlookup or something that will look up these months in another table, rather than keep typing them out in the formula?? Otherwise my formula will be very long.
So the info looks like this in excel
MARKETING 200612 -10
MARKETING 200701 -25
MARKETING 200708 -50
ECONOMICS 200709 -30
The info goes on and on. The two variables are the MARKETING column and the month column. My problem is that I would like a seperate table that can be the months. So 200612 and 200701 is one table, and 200708 and 200709 is another table. The table changes often so I dont want to mess with the formulas, rather a table.
View 5 Replies
View Related
Apr 28, 2012
I am having such a difficult time creating a macro that will reduce the 5+hours I have to spend each week manually copying & pasting all of this data. I making an IMMENSE difference in this worker bee's life!
I have a workbook with two sheets (Sheet1 & Sheet2). Sheet1 has license #'s in column A and the state that the license belongs to in column B like this:
COLUMN ACOLUMN B11111Alaska11112Alabama11113Arkansas11114Arkansas
Sheet2 has three columns. Column A has the license #'s, column B has the state that the license belongs to and Columns C shows a line-of-authority tied to that license #.
COLUMN ACOLUMN BCOLUMN
C11111AlaskaProperty11111AlaskaCasualty11112AlaskaLife11112AlaskaHealth11112
AlabamaProperty11112AlabamaCasualty11113ArkansasLife11113ArkansasHealth11114
ArkansasLife11114ArkansasHealth12345ArizonaProperty
I'm trying to write a macro that will compare the license # and state in Sheet1 to the license # and state in Sheet2. If it matches, append the contents of Column C to the corresponding row in Sheet1.
Here's the thing...Sheet2 contains the entries for all licenses in the company (so this table is HUGE). And there are multiple entries for each state license # (notice how there's two entries above for AK license # 11111 - one for the Property line and one for the Casualty line.
After my macro is run, I want Sheet1 to show all the lines-of-authority on a single line. So if I ran my macro on the above example, after it's run I would have this in Sheet1:
COLUMN ACOLUMN BCOLUMN
C11111AlaskaProperty Casualty11112AlabamaProperty Casualty11113
ArkansasLife Health11114ArkansasLife Health
View 5 Replies
View Related
May 12, 2014
Trying to create a dynamic vba code that searches the second last active column for a particular criteria. In this case it is the value "901".
If the criteria is met it will search the row to see if value is >=0 then change to zero where applicable.
In the example, I have a loop which searches Column 6 for the matching criteria. I want to change from Column 6 to "second last column" which is Dynamic to allow for more column to search through.
The two codes below search through column "A" and Column "B". Is there anyway to make this Dynamic also so it searches from Column "A" to the 4th Last Active Column.
So the Column to check for Matching Criteria is "Second Last Column" The Data to Loop through will be from Column A to the "Fourth Last Active Column".
Public Sub ChangeCellValue_TwoConditions_ColA()
Dim Last_Row As Long
Dim iLoop As Long
Last_Row = ActiveSheet.Range("A65536").End(xlUp).Row + 1
For iLoop = Last_Row To 1 Step -1
If (trim(ActiveSheet.Cells(iLoop, 1).Value) >= 0) And (trim(ActiveSheet.Cells(iLoop, 6).Value) = "901") Then
ActiveSheet.Cells(iLoop, 1).Value = "0"
[code].....
View 3 Replies
View Related
May 22, 2009
I am trying to search for multiple values in a cell with Multiple criteria.
E.g: Please find attached the sample excel data.
I tried using VLOOKUP which is not working as I am having multiple values in column A.
View 9 Replies
View Related
May 28, 2014
I have a Excel 2010 workbook used to rota in a large amount of staff for a call centre, which is split into four teams. Each sheet corresponds to a month of the calendar year eg Jan201, Feb 2014 etc..
What im trying to do is put in a sheet at the front of the workbook that I can select the team, which populates the list of staff in that team and then checking across a specified date range gives the shifts that those respective staff will be working for the set time period (probably be looking at a seven day period and a 1 month period). (This in turn will be printed out to give to the staff members.)
View 2 Replies
View Related
Feb 14, 2010
I have a spreadsheet with a database search function incorporated. Whilst this works fine, I was wondering if there is a way to combine the current single query searches into one search macro. I have setup a userform called CriteriaSearch that I would like to use to search my database tab. An example search would be:
User requires someone who speaks French (5), is female, has a rank of 3, is available immediately and has notes/keywords/keyphrases of "excellent linguistic and cultural knowledge". The appropriate fields on the userform are populated accordingly. The user selects search and a macro runs to find matching records. To make things slightly easier, the criteria (Language, Gender, Rank etc) all have fixed column references.
View 2 Replies
View Related
May 30, 2007
I need to bring in values into one worksheet from another worksheet using row 1 and column A as criteria. I have previously done this using Sumproduct. However the complicated issue with this new worksheet is the setout. The source worksheet (see attached) has the actual data spread across columns. How can I bring in the values to the 'Summary' sheet from the '0607' sheet using the Employee number and the seg code as 2 criterias to lookup and bring in the resulting data (which is spread out in the purple area in the '0607' worksheet).
View 8 Replies
View Related
Feb 26, 2014
I am looking to search in a table (say 4 columns) corresponding to multiple criterion (one for every column except fourth) and returning the values which are numerous (from column 4). I have tried the INDEX function but it only gives me one of the many cells. I am working on a table with +20000 cells per column
View 3 Replies
View Related
Jun 21, 2013
I need a formula that will tell me what Tracking # in the spreadsheet has multiple dispositions and what those disposition are. I need the formula to use column and cell coordinates and not using word searches.
example
Tracking # Req.# Disposition
1490069 045 snakes
1015907 047 candy bars
1488026 044 chickens
1015907 047 sand paper
1015907 001 fruitcups
1490129 046 cupcakes
1484817 043 Cats and dogs
View 2 Replies
View Related
Oct 5, 2011
I want to display multiple results based on 2 search parameters, where by when I enter flight number and Dep. Airport it matches the trip numbers and dispalys all the rows matching all flight number and Dep. airport based on the unique trip number.
Notice: - one trip number could consist of multi flight sectors.
1) Explanation of the data :
MMM1805 = flight Number
935 = dep. time
$AS41 = registration code
[Code].....
View 9 Replies
View Related
Apr 9, 2012
On my spreadsheet i want to find the results from 2 criteria that i entered.
My search criteria are "Oostbos" and "AA8", and excel has to find this from another spreadsheet that i made for rostering.
OostbosN3 evelineAA8N3 evelineAA8N2 MargaAA7
The problem is that i have multiple shifts with the "AA8" criteria, but my function only finds the first one.
I used the following function:
Code:
=IF(C8="";"";INDEX('Afdeling PG PH'!$A$6:$A$28;MATCH(C8&$A$7;'Afdeling PG PH'!$B$6:$B$20&'Afdeling PG PH'!$AI$6:$AI$20;0)))
Also when the AA8 cel is empty that i doesn't show anything.
How the second N3 eveline, shows the 2nd result and so on.
View 1 Replies
View Related
Aug 29, 2013
An example of my data is:
Column A Column B Column C
ABC DEF Answer 1
ABC GHI Answer 2
ABC JKL Answer 3
I need a formula that checks both column A for text (ABC) and column B for text (JKL, two different criteria) and give 'Answer 3'. There will a lot of data and it will not always be the bottom answer.
View 2 Replies
View Related
Oct 30, 2013
I have thought about recording a vlookup but this might be messy and prone to errors. Is there anyway to search through multiple sheets for a value (code) in Col A sheet 1. And return X in sheet 1 in the next column.
View 2 Replies
View Related
Feb 17, 2009
i want a macro to search all of column A in multiple sheets for a date that has been put into an input box.
the matching dates have data corresponding to them in that row. i want the date and data from the row to then be copied into a new sheet.
View 4 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
Mar 9, 2009
I'm trying to figure a formula that provides a numerical value when certain criteria are met across a range of cells. The criteria are numerous though and here is where I'm having trouble.
For example, the formula needs to look at Cell A5, then at a range of cells (C5,G5, E5, I5) and depending on the value of those cells provide a predetermine result in Cell K5.
For example, IF (A5 = 'A', and C5=1 or G5=1 or E5=1 or I5 = 1, then P5 must = 15), or if (A5 = 'A', and C5 or G5 or E5 or I5 = 2, then P5 must = 10), or if (A5 = A, and either C5 or G5 or E5 or I5 = 3, then P5 must = 6), or if (A5 = A, and either C5 or G5 or E5 or I5 = 4 then P5 must = 2), or if (A5 = A, and C5 or G5 or E5 or I5 = 5, then P5 must = 1)…….this needs to be duplicated for a list of possiblities.
View 6 Replies
View Related
Dec 26, 2012
I'm making a schedule grid has varying start & stop times. I've made a table so the people using it can check that there is overlap when they are scheduling themselves and the members of their shift.
The rows have the employees and the columns has the hours, (one row for each hour in the year 2013)
Each employee has a 1 under each hour where they work and they can easily see where their shift overlaps onto the next persons and by how many hours. I also have a row on the bottom that checks how many people are on at any given time so everyone can check that we have a minimum number of people on and a maximum number as well.
As it is pretty difficult to look at this grid as it is, i made a 2nd table which we could print to see which days we shoud come in and what time. (with each column being the day/ date and the cell having the time frame one comes in.)
The problem i have is that if any adjustments are done to the first table, (with the hourly columns) it isn't reflected in the 2nd table.
I've created a LOOKUP table with each hour/ shift, but i do not know how to have lookup check the starting hour, the ending hour, and what date it is.
I was considering placing a look-up formula in each cel for each employee for each day for the entire year, i just don't know what the formula should look like.
View 2 Replies
View Related
Dec 27, 2012
I have a spread sheet with multiple columns. I need a formula to look at criteria from three specific columns in the spread sheet and spit out a description from a key I created. Here is an example.
This is the key I created:
Record Kind
Group
Group Name
Category
[Code] .....
The spreadsheet has the same columns so I need a formule to look in the spreadsheet and if it sees each combination as in the key to spit out the category name from the key.
View 1 Replies
View Related
Jun 9, 2014
I'd like to extract the data from Sheet 2 (Data) that falls within the selected date range but the formula I've entered in F$9 (see below) is giving me an error
=IF(ROWS(A$9:A9)>$A$5,"",INDEX(Data!A$2:A$387,SMALL(IF((Data!$A$2:$A$320>=$A$2)*(Data!$A$2:$A$320<=$B$2)*(Data!$B$2:$B$320=$C$2),ROW(Data!$A$2:$A$334)-ROW(Data!$A$2)+1),ROWS(A$9:A9))))
test1.xlsx
View 3 Replies
View Related
Nov 10, 2009
I have been working on a variation of this for a long time and I cannot get it to work.
I am trying to depending on the textbox selection get the proper ROW from DEALLIST to show up.
View 2 Replies
View Related
Jul 31, 2014
I have created a userform which searches from the data using the value that i put in text box.
E.g. if i put student i.d in textbox1 all other textbox show me the data like name contact detail etc.
But I want one more criteria. i want to add one more textbox to search for e.g either i put student i.d or locker no whereas right now i can only put student ID.
View 1 Replies
View Related
Sep 24, 2013
I have 2 columns ,A and B, in a table and text strings in C. Each table row A1 and B1 needs to be compared and matched with the text string C:C. Only if both, A1 and B1, are found in the same string, say C5, it has to return A1&B1. If not, return "Not Found".
Several Obsticles:
1. A and B are text and number
2. C is text with different numbers saved as text and words all mixed up
3. There is no defined format for the text, it is different in each string
A
B
C
-500.000,00
Banana Apple
Data: 1 MANGOGrape 0000000 800,000.00 EUR something something
-800.000,00
Mango Grape
Something your Ref: 8750ours R ef: TG0PPM000000743 500,000.00EUR zu Gunsten Banana Appl e VVA
With all these different formats and inconsistency, is it even possible?
View 2 Replies
View Related
May 17, 2013
I have attached a demo spreadsheet to define the problem.
I wish to populate column K (Risk Rating) with data retrieved from the table, based on the corresponding information from Columns I & J.
e.g. I5 + J5 = Short term illness or injury + Unlikely. This corresponds to cell E9 (11) in the table so required response in K5 would be 11.
Help Book1.xlsx
View 1 Replies
View Related
