Multiple Lookup Vlaues
Mar 1, 2009Is it possible to use multiple lookup values in an array with multiple, uncontiguous data? The sample spreadsheet may explain it a bit better.
View 4 Replies
ADVERTISEMENT
INDEX/MATCH Function To Retrieve Vlaues From A Table And AVERAGE Them (arrays)
Mar 26, 2009I have 2 sheets recording feedback and summarising the overall percentages for each trainer.
My boss wants me to do this by month!?!?!
I have created a third sheet but I am not sure how to do the following:
Look up the trainer name
Look up the month
identify the percentages for each category
create an overall average of these percentages
So for example if Jon smith trained twice in JAN getting 100% and 50% in cat1, it would display 75% in the cat 1 cell and so on.
Multiple Lookup Values Rows And Columns To Lookup Single Target Column On Right End?
Apr 7, 2014I 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.
VLookup - Single Value Lookup Returning Multiple Records Into Multiple Columns
Feb 7, 2014Certification 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.
Excel 2010 :: Lookup Multiple Criteria Across Multiple Sheets?
May 28, 2014I 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.)
Lookup Multiple Same Value And Return Multiple Corresponding Value In Ascending Order
Oct 9, 2008I have a problem with the formula that look up multiple records with the same values and return multiple corresponding values in ascending order. I am using Excel 2003 and it is a bit complicated to explain so I have attached a sample spreadsheet to show what I mean.
What I want was after I have sorted the occurrence value in column E based on column B and I want to correspond the Rank in column D based on column A in ascending order for the same occurrence value in column E.
Eg: There is two occurrences for number 1 at E3 and E4, and three occurrences for number 2 at E5, E6 and E7 in column E. Then the Rank for the first occurrence for number 1 in D3 should be ranking 6 and the second occurrence for number 1 in D4 should be ranking 7, so the Rank for the first occurrence for number 2 in D5 should be ranking 3, D6 should be ranking 4 and D7 should be ranking 9 based on column A and B, etc.
Use INDEX To Lookup Multiple Values In Multiple List
Dec 8, 2013I am using the below array formula in G2 (that I then drag across) to show the score for all the times "mike" appears. I would like to match all the times "mike" OR "red" appears, so that the value in K2 is "99".
=INDEX($A$2:$C$9999,SMALL(IF($A$2:$A$9999=$E2,ROW($A$2:$A$9999)-1,"hh"),COLUMNS($G2:G2)),2)
A
B
C
D
E
F
G
H
I
J
K
L
1
name
score
color
[Code] ..........
Multiple Parameter Lookup For Multiple Table Ranges
Jun 15, 2008In the attached file i have multiple tables for different types of conservatory roofs (16 of them in total). The ranges at the top and side relate to milimeter measurements and the data in the middle relate to the price for that sized conservatory roof. The table works where the two ranges intercept each other. I have a formula to do this for one of the tables only. What i would like is a way of choosing which type of roof to use (i.e. which table to use) and then to be able to input the measurements and the price to be displayed. All of this needs to be done in one query so its as user friendly as possible. i've had is to use a pivot table, i feel it is not possible to use a pivot table to do this sort if thing after research into them, although i am un-familiar in the making of them
View 4 Replies View RelatedMultiple Column Lookup Across Multiple Sheets
Apr 11, 2007After going through multiple threads in the forums, I got this code to do a multiple VLOOKUP method.
=IF(E2+F2=0,"",INDEX(C2:C10,MATCH(1,(A2:A10=E2)*(B2:B10=F2),0)))
It works perfect on a sample sheet. But when im trying to implement it in a sheet with too much data, it always fails.
I have attached the sheet I am trying the formula on. I have grayed down the columns which needs formula's. The data is picked out from the second sheet.
This is how I have modified the formula to suit me..
=INDEX(Data!$G$2:$G$663,MATCH(1,(Data!$A$2:$A$663=$C2)*(LEFT(TEXT(Data!$E$2:$E$663,"mm/dd/yy hh:mm:ss"),8)=$I2),0))
<<Please note that all the dates and numbers in the sheet are in "text" format for ease of use>>
Multiple Lookup On One Row
May 26, 2007I am trying to solve a big problem for a project that I have to complete and for hours now I have searching for help as I run over this website. I will place a photo below and explain what the problem is...
I have this row that has 105 cells (I only put some of them for an example) and I have to find from these 105 cells how many 1,2,3,4,5,6,7,8 and 9 exist.
Is this possible to be found through a command?
Multiple Lookup And Sum
Sep 13, 2007I want to look up dates, list, two different variables (i called it D and P) and add the Ds and Ps which happen within the columns (it may have to skip columns and add). look at the attached image for the problem. i tried various combinations of sumifs to no avail. Is there a VBA code for performing this.
in the problem: list1 looks at the list1 and the date range from the bottom and adds the corresponding D and P values from Jan 2005 to Apr 2005. dates and lists will vary and are inputs.
Multiple Lookup
Jul 1, 2006I am trying to perform a multiple look up that will look for a value in column B then go over two columns and look for a value then return a value several columns over. Attached is a worksheet with an example.
View 4 Replies View RelatedMultiple Sheet Lookup
Feb 22, 2014I am trying to construct a way to return a set of values from multiple sheets onto one overview sheet, based on just changing a week number in one cell. I have attached a basic form sheet.
In the "results" sheet I would like to change the week number 1, 2, etc and with that change, return the values in C9, C11, F11, J11, M11 to refer to the worksheet of that week number
Lookup With Multiple Possible Returns
Mar 31, 2009I need to lookup a Order Number and return all associated Item Numbers. My spreadsheet has about 10,000 lines and these Order Numbers could have as little as 1 or as many as 15 Item Numbers associated. I would like to have a drop down on each Order Number that shows the Item Numbers.
View 3 Replies View RelatedLookup With Multiple Rows.
Sep 22, 2009I have been trying to auto update a table by looking up information from a table on top. However, for the top table there are multiple entries in the same column, and my look up only checks the first row and ignores the rest.
My formula at this point is: =IF(ISERROR(INDEX($C$3:$H$33, MATCH("X", I$3:I$33, FALSE), 4))=TRUE, "", IF(INDEX($C$3:$H$33, MATCH("X", I$3:I$33, FALSE), 4)=0, "", INDEX($C$3:$H$33, MATCH("X", I$3:I$33, FALSE), 4))).
If you can take a look at my attachment, this formula is in the cells I40:W40. Eventually, I will want to populate the entire bottom table with the same formula.
Lookup Across Multiple Sheets
May 3, 2013I have two workbooks of clinical data: one main of 1,400,000 rows and one small of 30,000 rows. What I want to do is compare the entries in small to find out whether they exist in main. All I need is a TRUE/FALSE result.
Eventually I want to run a comparison of :
Unique number, surname, firstname, DoB and gender. (amusing, excelforum will not allow me to type ***). For now I am just trying to figure out how to do it with the unique number.
I have got the hang of using the vlookup function, but have now found that it cannot search across multiple worksheets. Main is spread across 16 worksheets.
Multiple Value Array Lookup?
Jul 14, 2014I am trying to write a function that will look up values in an array, and then return a value from a separate array. where:
Column A has a name and dates:..Column B has a number associated with the date (0 or 1)
"John" ..............................................0
6/1 ..............................................0
6/2 ..............................................1
6/3... ..............................................0
6/30 ..............................................1
I am trying to make a summary table that shows the dates associated with each time "1" occurs
So it will look like:
column C: ...... Column D: ...... Column E:......Column F:
John.................6/2...............6/30............... N/A
Bill ............... 6/1...............6/2............... 6/30
Fred ...............N/A ...............N/A...............N/A
and so on:
I've tried excel formulas, but can't get the right date returned.
Lookup With Multiple Conditions
Dec 3, 2005way to return a value from a data table by specifying 3 conditions to be met.
Eg. My data table is in cells W1:Z100
(Column names = Track, Distance, Class, Time)
I want to be able to return the time value, based on track, distance and class values.
Multiple Worksheet Lookup
Oct 17, 2008I have a workbook with 3 workshhets. In the first worksheet I have companies in a ranked order. Based on the company number, I need to return a value from another worksheet that also has the company number as the key.
The lookup worksheet is FY2008 with the company number in column D and the value to be returned in column F. I know it is simple for you Excel gurus but I am still getting my feet wet.
Multiple Criteria Lookup ..?
Mar 9, 2009I'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.
Lookup In Multiple Columns
Aug 12, 2009I have been already fighting for about a week with this problem. I attached the Excel-file. I have three input data and as output I want to get also three different data. For Vendor I use the VLOOKUP function. For agreement# I have used (not in this file) CONCATENATE by adding an extra column to the data table and then use VLOOKUP.
The actual problem is to find the price because it is based on the three input data. I can't seem to find a function for it. I've tried INDEX, MATCH, IF, nested them for as far as my knowledge reaches.
The problem lies for as far as I know in the del.date cell. For example, input date is:
Client#:912345, Product:E569831, del.date: 16.7. (July 16th). This input date should return cell G12.
Multiple Output From Lookup
Dec 25, 2012Writing a formule to lookup for a value and return more than one result,
Example
Col A has Names, Col B has ID number, in Col C I need the output to look for names in col A and return the ID from Col B, I can use a VLOOKUP for this but the problem when col A has a name repeating more that once, In this serinario the lookup picks the first name and gives the first ID
Here is how I want the out put to be as:
Names ID Output
John 1 1,3,6
Mathew 2 2
John 3 1,3,6
Mike 4 4
Bob 5 5
John 6 1,3,6
The output should have the ID of all same names
Using LOOKUP With Multiple Criteria
Dec 26, 2012I'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.
Lookup With Multiple Criteria
Dec 27, 2012I 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.
Multiple Items To Lookup
Jan 30, 2007I have a list of products with parts for each. I made a drop down of each product and what I want to happen is after I select the product it will autopopulate all the part info for that specific product. Each part has a description, sku, quantity and price.
There are up to 10 parts per product so I dont know how to do this. Normally I would use vlookup or something to that degree but I dont know how to display a bunch of information.
Possible Multiple Rows LOOKUP
Jan 10, 2008I have a worksheet which Sorted in ColA contains Product Id#, ColB contains Vendor, ColC contains Grade# (1 thru 6). Now there may be multiple Vendors (ColB) for same Product # (ColA) but with different Grade# ColC).
I would hope if you can please help me formulate for ColD to pull (list) ALL Vendors for the same Product# (there maybe as many as 6) PROVIDING that the Grade# (ColC) is either 3 or 4.
Lookup With Multiple Sheets
Mar 12, 2008I have a workbook that has separate tabs for each month. In cell D1 in each sheet is the first day of each month (1/1/08) then in E1 (1/2/08) until the end of the month.
In each sheets - the total for each day is in D30 then E30, etc...depending on which column the last day of the month ended up in.
Then I have a "Report" on using its own worksheet in the same workbook. I would like to be able to type in a date in cell F4 "Report" tab and then for cell A9 "Report" tab to be able to search through JAN D1-?, then FEB D1-?, then MAR D1-?, etc. until it finds the date. Then I would like for it to return the corresponding value found in D30 or E30, etc.
Lookup And Return Multiple
Jan 30, 2009I have a spreadsheet similiar to the below:
COLUMN A.....COLUMN B
Person 1.......Process 1
Person 1.......Process 1
Person 1.......Process 2
Person 1.......Process 1
Person 1.......Process 2
Person 1.......Process 3
Person 2.......Process 1
Person 2.......Process 4
Person 2.......Process 1
and so on.
In the list, the "People" aren't neccessarily in order, for example, later in the list, Person 1 appears again. There are a total of 10 processes, and any person can do any number of each.
What I ideally want is in Columns D, E, and F, is something like the following:
COLUMN D.....COLUMN E....COLUMN F
Person 1...........3.............Process 1
Person 1...........2.............Process 2
Person 1...........1.............Process 3
Person 2...........2.............Process 1
so a summary of the main data, showing how many each process each person has got.
I have, and failed miserably, getting arrays to work but that might just be down to my own incompetence. I have researched into Countif's and Lookups, but it's not quite working.
The data in columns D, E, and F needs to be fairly compact so ideally I wouldnt want any spare rows between the end of the summary data about Person 1 and the start of Person's 2 details.
Multiple Lookup Or Sumproduct
Jul 2, 2009My data range is E2:H15. A separte calculation would generate a number in C2. C4 uses Index(match) to finds the closest Vol match greater than or equalin column E. Column E is sorted Desending for the Match. The user then inputs two maximum values for W (column F) and H (col G).
The example problem should return 20 for L. Help! I have been trying every combination of index, match, lookup etc.
Sheet1 *ABCDEFGH1****VolWHL2*Minimum Vol463*800104203Lookup 1Closet Match Greater Than or Equal To480*64084204Lookup 2Input Max W =8*600103205Lookup 3Input Max H =3*600104156****48083207*Solve for L20*48084158****48064209****4501031510****4001041011****360831512****360632013****360641514****320841015****30010310Spreadsheet FormulasCellFormulaC3=INDEX(E2:E15,MATCH(C2,E2:E15,-1),1) Excel tables to the web >> Excel Jeanie HTML 4
Lookup With Multiple Results
Oct 29, 2009After a lot of searching and trying I still didn't get to something that works.
My situation:
sheet1
column A column B
true "abc"
true "bcd"
false "cde"
true "def"
false "efg"
false "fgh"
on sheet2, in cell A1, I would like to get all results from column B that correspond 'TRUE' in that row.
So, in cell A1 on sheet2, i would like to see "abc", "bcd", "def".
VLOOKUP returns only one result, of course. How do I solve this?
I'm not good at VB, so preferably only with formulas!