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 have = LOOKUP(REPT("z",255),CHOOSE({1,2},"",LOOKUP(C3,{1,2,3},{2,1,0}) )) in cell D3. C3=1 so I expected a result of D3=2. Instead I am getting a blank cell.
I am wondering if there is a way to drag a formula, for instance, down, and each cell that I drag down, it references one cell to the right.
Excel Example.png
I attached a photo -- I understand that this is an overly simplified example, but the idea is that it would reference the row as you drag down the column.
Is there a way to use the lookup function in reverse? The "lookup vector" on mine has to be descending, that is it it is a column going from high values at the top of the page to lower values at the bottom of the page. When using lookup function in this setup, excel gives me to wrong answer for some reason. If I make the lookup vector ascending, it works, but unfortunately the vector has to be descending in my worksheet.
I want to filldown this formula, but the comparison range ( sheet 2, columns A & B, rows 4-73) never changes. how can accomplish this so when i use ctrl-d, only L4 updates (to L5, L6, L7, etc..), but the range remains the same?
by default, filldown tool updates the range to A5:A74, A6:A75, A7:A76, etc
if it helps i'm using this forumla to: 1) take a value (L4) 2) find this value in the first range (somewhere in sheet 2 column A) 3) enter the corresponding value (sheet 2 column b, same row)
I want to find a number in my array and return the value in column A. Trouble is if the array goes past column R I get the wrong Answer. the formula I am using is
=LOOKUP($C$22,$B$2:$AC$18,$A$2:$A$18)
Some cells are blank I am trying to draw a raffle.
Here is my data and formulas below:
The lookup of value 209 produces correct results when I look up as far as column R after that I get the wrong answer.
I want to use the V lookup function to return a value, but rather than returning the value in the same row as the match in the specified column, I want to return the value in the cell below.
Eg.
=VLOOKUP(B397, A$267:H$351, 7, FALSE)
... but rather than returning the value in column 7 on the row that matched with B397, I want the field below.
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 am running a fairly complex simulation at work and have quite a few different functions in it. For the simulation to work properly, i need to have the Solver add-in and the Analysis ToolPak installed. Everytime that i try and use the simulation on a different computer (or if anyone else tries to run it) the add-ins must be enabled again. Is there a way to set up a macro to enable them when either i open the simulation (first preference) or when a button is pressed (2nd preference).
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:
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.
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 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?
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??
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
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.
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.
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.
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
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.
