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
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.
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
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.
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.
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
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.
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 RelatedI 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?
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.
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.
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.
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.
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.
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].....
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 RelatedI 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.
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 RelatedI 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
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.
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
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.
See attached for a clearer view : LOOKUP SEARCH.xlsx‎
View 10 Replies View RelatedI'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.
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....
I am trying to find a solution to a vlookup issue. I have attached to this post a sample excel file which will make it easier to understand. I have data in one table (Table 1) where the records consist of addresses with street field, city and zip. These records have to be flagged based on the street address (number and street name provided the city or zip is the same) with a flag called "Type"(as an example Type 1 or type 2, etc).
Than I have another table (Table 2 or the lookup table) that has unique adrresses with the "Type" field that needs to be brought over to Table 1. The issue that I run into is that addresses in Table 1 have variations such as 25 Main Street can be as 25 Main St., or 25 Main St, or 25 Main st., Apt 2, which all should be recognized as 25 Main Street. All we care is to flag 25 Main Street with it's appropriate flag from Table 2.
Data in Table 1 can be in the 100k or more records in some cases so any manual intervention would be very time consuming and not efficient. Now I've tried doing lookup only on the 3 or 4 left characters of this field but than you get thrown a curveball when the address is for example 25 Maitland Avenue, which will falsly flag them as 25 Main Street. Plus house numbers can be 25 Main, or 255 main 2554 Main or 2 Main, so it is not functional to use the left function nested within vlookup. This issue seems to be more of a logical validation, which I don't know how to approach. The best way to think about this is when you go print a priority label on the USPS website and they correctly validate your address eventhough you might have mistyped your address.
In the attached workbook, the following formula appears in column B of the "dropdown" tab. I want to basically do a two criteria lookup, using the values in column A and the value of cell B1, which is a dropdown based on a dynamic range consisting of the company names on the Discounts tab.(starting with cell E1, extending rightward) To make this easier for testing purposes, I'm curretly using cell E1 instead of B1, which is just text.
It seems like this ought to work, but somehow it doesn't:
=OFFSET(Discounts!$B$1,(MATCH($E$1,Discounts!$C$1:$AA$1,0)),(MATCH(A5,Discounts!$B$2:$B$200,0)),1,1)
i need to track tyres, i'll have to use the Serial Nos. of the tyres inscribed on them. What i require is to lookup the serial no. from Purchase/issue sheet to my Maintenance sheet. To do that there'll be 3 criterion to match to get the correct Serial No.
In my Purchase sheet suppose if a purchase a tyre and issue it to some vehicle on 11th September, i'll have its first inspection for maintenance after mebbe a month (no fixed intervals). So if i check on 10th October the lookup should actually find the greatest date lesser than the inspection date and keep it to that. I know that the lookup function would get the last unit in case of duplicate entries but that becomes dynamic and the serial no. would change once theres another entry matching the 3 criteria. And dat would mess up my previous record.
Attached is my sample workbook. There would normally be 600+ employees with multiple rows per employee. I would like Cell O3 in the Premium Calculation Worksheet to look at the Premium Contribution Report, and if Row A contains the employee number (A3) AND row C contains "H&D" I would like it to sum row E.
I included the sumproduct formula I tried to put together but I'm getting an error, so I'm not sure what I've done wrong. The reason I have it referencing "O2" instead of just inputting "H&D" is that O2 could be any number of plans - I have multiple rows with different plans and I need it to pull in all the data.
I have table product that include (wide 1,wide 2,thick 1,thick 2,long 1,long 2,Price) And I have table transaction than have wide, thick, long and price)
I want that price in table transaction fill automaticly from table product where ( wide between wide 1 and wide 2 ) and
( thick between thick 1 and thick 2 ) and ( long between long 1 and long 2 )
excelformula.gif
pricing.xls
