Lookup Value Using 2 Criteria - Criteria Moves Between Columns
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
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
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
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
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
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
May 16, 2014
I have duplicate product ids on same date with different unit sold and need to extract all the unit sold for a product id on a particular date. How to concatenate a formula? The array formula that I use does not work:
=IF(MIN(IF(UnitsSoldOnlineVlookup!$A$2:$A$980=A2,
IF(UnitsSoldOnlineVlookup!$C$2:$C$980=B2,
ROW(UnitsSoldOnlineVlookup!$A$2:$A$980)-
[Code] .....
The formula needs to lookup units sold in UnitssoldOnlineVlookup tab and enter them in Units sold column (highlighted in orange in the attached UnitsSold workbook) in Performance tab.
UnitsSold Workbook.xls
View 8 Replies
View Related
Apr 6, 2009
I have attached a workbook with 2 worksheets Refund & Factor. In brief I wish to work out the refund on a ticket. To do this I have entered the start date & surrender date. In C29 I have a formula that gives the datediff in months & days. I then have some VBA that extracts the numbers and enters them into D29 & E29.
I now need a formula to look at the factor sheet and find the intersection between 3 (months) & 19 (days) which is 13.96 and copy this to B36 on the refund sheet.
View 2 Replies
View Related
Nov 13, 2009
Currently using Lookup(2,1/...) to match 2 criteria before giving me my end result. It works perfectly. I'm now needing to match 3 criteria... is there an easy way to modify this to allow it? My Current formula looks like
=LOOKUP(2,1/(Upload!$D$1:$D$25&":"&Upload!$F$1:$F$25="Gordon Brown "&":Downing"),Upload!$I$1:$I$25)
And as well as matching Gordon Brown, Downing, I'd like to match the word Motor which it would search in Column E of the Upload sheet.
View 2 Replies
View Related
Oct 21, 2006
i am having problem with two-way lookup criteria.I have a chart for permissible weight as per height of a person.But there is another criteria of age-e.g.col A (A8:A25)has the various height-groups(from156cm to 190cm) and row 7 (B7:I7) has the age-groups(from 15 years to 48 years) .All figures of weight are within B8:I25.Now if i want to get the permissible weight of a person whose height and age are known ,how do i get using the lookup function?
View 3 Replies
View Related
Nov 23, 2007
I need some VBA help for my project...
attached is my sample spreadsheet...
I need the excel to be program so that when i input the 'Grade' and 'T', it will automatically select the py value.
Say i select the Grade S275, and input T= 10.4, thus excel will look up at the S275 table, then to the T< 16 (because T=10.4) and thus select '275' and input it at cell E7.
if i put input Grade S355 and input T=43, then it will look up at the S355 table, then to T<63, and then select '335' and inout at cell E7
View 3 Replies
View Related
Jan 10, 2008
I am having some difficulty creating a lookup that takes two values and uses them both to perform an action analagous to a vlookup. That is, use the two input criteria to select a roow of data, and then taking an input column number to return the resulting cell's value. I cannot concatonate and create an index because my second value is a number that may not match exactly. I also need it not to involve too many loops as it will be used thousands of times on the same sheet on my client's older machines.
I have seen the listmatch and matchinglists examples on Ozgrid and they do not quite address my need. I am not an excel novice, but my VBA is not that strong. the codes I have written to date ( attached) will not allow my data set to be on a different tab than the tab on which the function is used, as i require.
View 6 Replies
View Related
Mar 31, 2008
I am trying to lookup the value in a table based on two criteria. For the purpose of illustration, I attached the file here. What I am trying to do is that I want to fill Table 2 with data from Table 1 which match the company and the dates. If all the dates match for the companies, that problem would be much easier, However, as you can see the dates from each company do not match on the same row,totally massed things up. In my real file I have 70 companies, any one have a solution for this??
View 9 Replies
View Related
Jun 6, 2014
I have two workbooks. I'll call them wkbk1 and wkbk2.
I am looking at three cells in the same row in wkbk1.
I need to identify which row in wkbk2 contains those values and then return a value from a cell in the same row in wkbk2.
How do I structure this look up?
View 5 Replies
View Related
Nov 10, 2009
I am looking up values L, M, and S from a seperate worksheet based on two factors. age in months, and gender. I have read enough to figure out how to lookup values L,M,S one at a time using a vectorlookup, from one worksheet to another. However, the LMS values being looked up are for females only. I know I must get the gender codes worked in but not sure how to work this in.
I don't know if I need an and If function, to meet the gender (1male, 2 female) or what.
View 11 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
Aug 17, 2013
I have an engineering spec table. Down 12 columns I have height in metres of a post and across the rows I have square metres with 5, 10, 15, 20 and 25 being the sizes.
The engineering spec of the post type is in the table. There are some duplication of post types so post heights of 1m, 2.1m, 2.4m, 2.7m, 3m, 3.3m and 3.6m are the same, then they change. Its a similar thing in the other column headers for 10sq.m 15sq.m etc.
I need a formula so that if the post height of 3m was chosen and the sq.metres was 15 then it would tell me the post type from the table. Or if the post height was 4.8m and sq.metres was 25 it would tell me.
The post type is an engineering code of numbers and letters with no spaces.
View 5 Replies
View Related
Apr 29, 2014
trying to lookup data based on 2 criteria. On sheet A I need to lookup "Date of Reason" and "AMT" based on Employee Name and if there is something listed in the Reason Codes Column from Sheet A. So on sheet A employee Vandiver has a reason code = Absent so I need to pick up the date and amount from sheet B = 4/21/2014 and 8. I tried the formulas listed below but can't seem to get them to work. Also need to note that sometimes the Reason Code column in Sheet A may have multiple listings so it would be better if formula used a "not blank" in it. That's what I tried with the first "IF" formulas using "=" as there are formulas in the reason codes column in Sheet A.Sheet A
Employee
Mon
Tue
[Code].....
View 8 Replies
View Related
Jan 4, 2007
I have a workbook which has a number of activity sheets which calculate a cost against an individual on a number of tasks they may do. There are a number of worksheets and individuals might appear on a number of these sheets depending on tasks. I have a summary sheet which lists the individuals by code (which appears on the task sheets with their name) and I would like to have a function which looks at the individuals code, finds it on each activity sheet and adds all costs up to provide a total. I know I can do a series of Vlookups and SUM Formula these together but wondered if there is a why of combining functions (or another function) which would do this.
View 6 Replies
View Related
Dec 1, 2007
I need to take a value from an adjacent cell and automatically enter that value in a specific cell in table on a second worksheet. The cell must match two criteria specified in the previous two ceels to the value.
The table is made up of dates and colours, it seems simple in my head but I'm not sure where to start, i would like to use a macro so when a specific key is pressed the information is automatically entered into the relevant cell.
View 9 Replies
View Related
Dec 19, 2007
I setup a Vlookup formula (in sheet 1) to find the figure in column C (in sheet 2) based on the company name and branch name but the result is incorrect.
View 3 Replies
View Related
May 31, 2008
I have two pivot tables (for sales and purchases) with three columns each, ie: company 1, company 2 and amount
I need to reconcile that for the internal purchases and sales the sale value is the same as the purchase.
So in first pivot table (purchases) I have one row with internal transactions where company 1 = AZS, company 2 =YDR, value 100
In second pivot table (sales) I have one row with internal transactions company 1= YDR, company 2 = AZS, value 95
I would like to develop a macro that would check automatically the combinations of companies and find out if there are differences between sales and purchase. So in the example it should show that there is 5 usd more in purchases. Hopefully normally is zero
View 3 Replies
View Related
Jul 30, 2008
I have a report that I paste into excel. The report contains 4 columns. Column A contains a numeric value. The numeric value relates to a geographical area and the same numeric value may show up more than once. The reason the numeric value may show up more than once is because in column B there is a list of dates showing when a percentage in column D become effective. I have attached an example of the report.
What I am looking for is a was where the user can enter a geographic number in cell J4 and a control date in cell J5 and have cell J7 display the percentage value. I need the lookup to look up the correct area and then use the control date to find the corresponding percentage. So if Area 1 (numeric value) shows up in Column A in rows 2 through 5, the control date will determine which row to pull the percentage from using the effective dates in column B.
View 3 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
Jun 3, 2014
I have a cell that contains a long string of text.
I want to be able to lookup in it to see if any word from a list is in it, if it is to return text dependant on which word is in that cell.
Seems like it should be easy but looking up the multiple values is making it difficult.
View 6 Replies
View Related
Jul 22, 2014
See attached for a clearer view : LOOKUP SEARCH.xlsx‎
View 10 Replies
View Related
Aug 6, 2014
I've attached a sample of the data I'm using.
I have two spreadsheets (the samples for which I have shown side by side in Sheet 1 of the attached file).
Spreadsheet 1 is about 30,000 rows and too large for me to change the formatting and structure.
Spreadsheet 2 is the output I need and the format is required by other stakeholders.
In spreadsheet 1 I want to sum quantity in stock for Type 1, Type 2 and Type 3 for each product and allocate it to spreadsheet 2 according to the month in which the product expires. For example, there will be a total of 92 units of product 413302 which will expire in Nov, 2014. Therefore I want 92 to be placed in cell N6 of Spreadsheet 2.
Unfortunately the product number is not unique - there are multiple sub products in spreadsheet 1 but they all have the same quantities of stock. The sub products are referenced in other parts of the report so I can't consolidate by Product Number. This also prevents me from using the SUMIFS function as it will duplicate the number found in the sub products.
What I need, perhaps in a combination of functions, is to find the first instance of product 413302 in Spreadsheet 1 that is expiring in Nov 14, sum the product types and give the result in cell N6 of Spreadsheet 2.
View 3 Replies
View Related
May 28, 2009
Could ANYONE help me solve this formula based on critera sheet name.
On my main sheet workbook i have all the sheet names in column A with their description in column B. In column C i would like to have sort of this formula with result as total.
eg....
View 8 Replies
View Related
