I need to determine the shipping price of, say, a fruit based on its weight. I'd like to do this by looking at a matrix that lists fruit type (column E), weight range (column F/G), and resulting shipping price.
View 8 RepliesI am trying to build a staff roster. The staff rotate over a 4 week cycle. the name of the staff member, and their shift needs to be looked up from the key then matched with the particular week. the name and shift then need to populate specific cells.
I have attached the worksheet so you can see what i am trying to achieve.
I am creating a product feature catalog. I have created a dropdown with the names of the products. There are many details like price availability etc.
Now I have the product features in another sheet, something like this:
Product Name
Product Features
Product A
Good Battery Life
Product B
Big Screen
Product A
Bigger Processor
Product C
Low power consumption
I have selected the entire table and named it as "Feature".
How can I display the entire table by using the NAME feature i.e. using the reference "Feature". When I try to use =Feature i get the #VALUE error.
I have a table in one sheet as follows: .....
View 9 Replies View RelatedI wish to create a Look up that looks at a range of cells on the same line Eg C6:G6
Then looks at a different tab and completes the look up.
E.g.
TCR!A:B,2,FALSE
No within the cells C6:G6 i want the formula to only use the highest match
E.g.
C D E F G
070031070031#N/A#N/A070
#N/A#N/A#N/A#N/A070
So in the first line i want the result to use the number 070031 and in the second example i want it to use 070 for the look up.
I have data inputted into cells a-o in rows 1-250. I want to search, lets say row 1, (I do want to search all 250 rows if that info is relevant!) and if cell m is blank then I want to return all the info from row 1 into row 260. If there IS data in cell m then I want to return nothing. Bottom line I want to return all the data in all the rows that contains nothing in cell m.
View 3 Replies View RelatedI try to write the function that gets some "ref" and returns appropriate item.
For example: if I give to function "C3", it returns "AAA"
if I give to function "R18", it returns "BBB" (cause it between R15 to R26)
if I give to function "R9", it returns "BBB" also.
I have a row data corresponding to the measuring of load cell per min and I need to average the values per hour. So I have a column B for the date (from 1-01-2013 to 31-01-2013, column C for the time (0:00:00 to 23:59:00), and D de values per min I want to average. I have to do the same for the rest of the month of 2013 (February, ...., December).
I would like to know if there is a way to create a formula to calculate the average of the first 60 values (to get the average of the first hour of 1-01-2013), and then copy it to get the average of the following 60 (average of the second hour in 1-01-2013) and so on.
If there is no way to do it, I would like to know if I can do it using functions like average, match, index, offset, what would be the best match of those functions.
I also tried it by doing the analysis in another tab and using the function "averageifs" with two criterias: one for the date (example 01-01-2013) and another one for the hour (example 0:00:00), but it didnt work, it show error: #value. I inserted an extra column in the data tab with just the hour (example 0:00:00) in front of the corresponding column with (example 0:01:00, 00:02:00, etc)
Equation I used for this:
=AVERAGEIFS('Data (min)'!D$6:D$43206,'Data (min)'!$A$6:$A$44646,A6,'Data (min)'!$B$6:$B$44646,B6)
=AVERAGEIFS(TAB AND COLUMN WHERE THE RAW DATA IS,RANGE OF CRITERIA 1,CRITERIA 1,RANGE CRITERIA 2,CRITERIA 2)
I have one sheet "FF" which shows categories of companies (1-48) and each of these categories include several SIC numbers.
What I now want is to find the right category (1-48) for each SIC code in my second sheet.
is it posible to use lookup to complete a name range?
example:
if "joe" is a barbarian, sorcerer, druid, each class has features dependant on its class level, not the character level. so, if "joe" were a 9th level barbarian, 7th level sorcerer, 7th level druid, his character level would be 23.
each class is assigned its own name for its total levels. char_lvl_barbarian, char_lvl_sorcerer, char_lvl_druid.
is there a way to use lookup to match char_lvl_ to a given class type or do i need to type each individual class type level name range? i'm hoping for one formula that can allow me ease of use instead of a multitude of conditions.
TimeForce
00.2
0.50.5
11
1.52
24
2.54
34
3.54
44
4.54
54
5.54
64
6.51
70.3
for example i have this data I want to average range of some values in force column for which i only know range of values in TIME column..
For Example I want to average the values for which the values in time column are 0 to 1.5 ((0.2+.5+1+2)/4)
I deduct a formula but it is giving error AVERAGE(LOOKUP(0,A:A,B:B):LOOKUP(1.5,A:A,B:B))
I have two sheets in Excel as below format. I would like to lookup (fill) “issue to” column of sheet2 in respect to sheet1. Please is there any funcations?
Sheet1
chqno Fr chqno toIssue to
1000110050Branch-1
1005110100Branch-2
1010110150Branch-3
1015110200Branch-4
Sheet2
Chq noIssue to
10002
10006
10155
10120
10008
10035
10151
I am creating a list with Data>Validation>Allow>list and it requests the source for the list- all OK, but the list produced needs to depend on other factors, I have several named range's containing different lists.
I need to insert in the "list Source" box a cell reference, which contains a the name of the range ie "list46" and for the source to recognise this as a list to produce
Is it possible to lookup a named range to produce a list?
Sorry about the explanation very difficult to put in to words!
looking for a way to look in a column for a value, and then spit out the highest value in corresponding cells in a column further over
so i want to look at all the classes associated with the same case, and have excel spit out the highest CscR no associated with those cells that do match..
yes i could do it manually, but my actual file has like 4000 samples in the first column.
Is there a way that I can do a vlookup in 1 file and specify more than 1 data range to lookup the data in?
In this case I have one file to put the vlookup in and a second file with more than 1 tab and I want to have it search each of those tabs and return the result. The format of these sepatate tabs are to each other and for that matter, identicle to the main file.
I would like to match a value between a weight range.
For example:
0-0.5 gram is $2
0.5-1 gram is $3
So, if my weight is at 0.3, the formula should give $2.
I have a list of names in sheet1 column A and 3 more list of names on sheet2, sheet3 and sheet3. What I'm trying to do is find a match for each name on sheet1 column A on the other sheets and depending on which sheet it was found, I need a specific value returned to sheet1 column B.
For example, if A1 was found on sheet2, then on B1 I would need to return the value "Morning" but if it was found on sheet3, then I need the value "Afternoon" and if found on sheet3 I need the value "evening" returned to B1 and then the same for every other name on the list found on sheet1 column A.
What I tried was this.
This goes in Sheet1 B1 (then I would drag it down to search for the rest)
=IF(AND(MATCH(A1,'sheet2'!$E:$E,0)),"Morning",IF(AND(MATCH(A1,'sheet3'!$E:$E,0)),
"Afternoon", IF(AND(MATCH(A1,'sheet4'!$E:$E,0)),"Evening","Un-assigned"))
Now the problem with this is that it only works if there is a match on sheet2 but if there is no match, it just returns "N/A" and it does not move on to sheet3 and/or sheet4.
I also tried with vLOOKUP instead of MATCH and the same thing happens.
I also tried this and the only thing I get is "Un-Assigned"
=IF(AND(A1='sheet2'!E:E),"Morning",IF(AND(A1='sheet3'!E:E),"Afternoon",IF(AND(A1='sheet4'!E:E),"Evening","Un-Assigned")))
I'm trying to set a range to use in a Lookup function but it's not working. I've done this other times with no trouble and I don't see why it's not working now.
If Target.Row > 2 And Target.Count > 1 Then
For Each c In selection
If c.Column = 11 Then
' confirm that Activity ID is in the Reference file list
Set Lookup_Vector1 = Worksheets(c.Worksheet.Name).Range("Activity_ID")
Error occurs on the Set Lookup_Vector1 line, Run-time error '1004': Application-defined or object-defined error.
The range Activity_ID is defined in the current worksheet but exists in another workbook. The c.Worksheet.Name correctly gives the name of the worksheet where the range is defined.
I am trying to display part of a worksheets new sheet based on some criteria. I have a worksheet with a list. I have a date field in the list. I need to breakup the into several sheets based on the dates. I want all of items in the list with a February date put onto a tab titled February and all items on the list with a March date on a tab titled March. I haven't been able to find a funtion or code that will do this. The new tabs don't have to be created through automation. i can create those manually. i am looking for a formula or possible code to put on the new sheets that will look up and return specific rows from the main worksheet with the master list. i attached a sample of my worksheet. I need to search by Column O titled Date of Next Review.
View 4 Replies View RelatedI'm using an Array formula to lookup a particular value then return a related column value.
What I would like to do is Sum a related range of cells.
The formula I have used to look up the single cell is
{=INDEX('[PL.xls]Parts List'!A4:S205,MATCH(A63,'[PL.xls]Parts List'!A4:A205,0),7)}
So in this case the related value in column 7 is returned.
I would like to sum the related values in say columns 5 to 11
I have a workbook where road structure data is recorded i.e. bridges, culverts, etc.
One of the worksheets has data I need to lookup. The primary worksheet called “Structure_Data” has the ‘Road Number’ (RN) of a particular road and the ‘Road Running Distance’ (RRD) of a particular structure. A particular road may have many structures along its length.
Another worksheet called “Road_Number_of_Lanes” has data that that indicates how many lanes there are between particular RRDs.
Here is what I am trying to work out...
In the “Structure_Data” worksheet look at the RRD of a structure and the number of the road it is on, then look up the road number on the Road_Number_of_Lanes worksheet; look at the ‘Start RRD’ and the ‘End RRD’ and return the number of lanes for that particular RRD.
“Structure_Data” worksheet
Column C has the Road Number
Column X has the Road Running Distance
“Road_Number_of_Lanes” worksheet
Column A has the Road Number
Column C has the Start Road Running Distance
Column D has the End Road Running Distance
Column E has the number of traffic lanes
My problem is that for example Road Number 1000 starts at 0 and goes to 927.09 (approx 927 km long). There are 27 entries of RN 1000 i.e. from 0 to 411.69 there are 2 lanes, from 773.62 to 774.85 there are 3 lanes and from 774.85 to 778.09 it goes back to 2 lanes.
How can I find out how many lanes of traffic there is at a particular structures location.
How to lookup for greater than a given particular value in a list of data? Suppose the value to find is in cell C17 & the column to be looked for is F, in the attached sheet. Now the data range is from F8:L27. If the value in C17 is not present in Column L, then the value greater than the specifed value should be searched in Column L. I have tried using VLOOKUP, but cannot find for a range of values.
View 2 Replies View RelatedI have two values I need to find in a chart.
(See Attached)
I can do a Vertical Lookup but don't know how to search for the second value to identify how much to offset to retrieve the value.
THE FORMULA - IT FINDS THE LAST-OCCURRING NUMBER IN A ROW, AND ADDS 1 TO IT IF A CELL LOWER DOWN IN THE PRESENT COLUMN CONTAINS TEXT:
Here is a formula to find the previous occurrence of a number in Row 1, and to ADD 1 to it if in the same column (Column Z) as the present cell (Z1), there is NOT a blank in the specified cell (Z5)
=IF(ISBLANK(Z5),"",LOOKUP(10^100,$C1:Y1)+1)
This is quite nifty. Basically if Z5 says "go!"
then Z1 will find the last number in Row 1
- let's imagine F1 contains "300" as the last number so far in Row 1 -
and add 1 to it
- in this case Z1 will thus say "301"
THE NEXT CELL:
With each next cell in Row 1, the formula moves up an increment, except where specifically told to freeze ($). Thus Cell AA1, the next cell in Row 1 after Z1, will have the formula:
=IF(ISBLANK(AA5),"",LOOKUP(10^100,$C1:Z1)+1)
THE PROBLEM:
I don't want to extend Row 1 beyond Column Z. I want my spreadsheet to be more compact. So l want to continue the formula on another row. Let this be Row 43.
So, how do l set the formula to LOOKUP the last-occurring number in Row 1 between Column A - Column Z (i.e. $C1:$Z1) but then - if no number is found in that range - continue the search in Row 43 between Column C - Column F for example (i.e. $C43:F43)?
And how many times can l introduce a new row into the range within which to find the last-occurring number?
how to lookup some of my "range of data" in one cell.. please have a look at my sample workbook..Book.xlsx
View 7 Replies View RelatedI'm trying to sum a range of data consisting of 12 cells (each range is in a unique row going across adjacent columns). The range of data to be summed is based on a lookup value which is a column header - I want the range to start with this lookup value. In column B of my spreadsheet, I have a 'First Activity number code' going down...In Row 4, I have the same First Activity Codes going across the row (the column headers). In Column U, going down the column I am looking for my formula.
So For example, in row 5 of my spreadsheet, I am looking at 73 as my base code (the value in column B), and I want a range of data summed that lies in the same row where the column headers are 73 - 84 (12 rows) - always starting summing the range with the same number that is in column B. So in row 18, 78 is my base number; so I would want the sum of the numbers in the same row where the column headers are 78-89 ( 12 columns).
I am writing the code for a VLOOKUP in VBA..I was using the .Formula = "=VLOOKUP(LookupValue, LookupRange , Column No, 0 )"
But, the problem is that the LookupRange is to be done from different sheets everyday as the name of this sheet is going to be like 16th May,17th May etc.
The common thing is that this sheet is the adjacent sheet next to the one in which we are trying to get the VLOOKUP work...so what solution can i use.
I need to make a VLOOKUP in a sheet which I do not own or control the layout of. Therefore I cannot add any columns in the source to assist me in my lookup.
My VLOOKUP needs to look at two values/columns since the value in one column is not a unique identifier. This is easy in the value I want to lookup but not so easy in the lookup range in the source sheet. Ideally I would like to write my formula something like this:
=VLOOKUP(A2&B2;CONCATENATE('[OtherWorkbook.xlsx]Sheet1'!$M$2:$M$9;'[OtherWorkbook.xlsx]Sheet1'!$N$2:$N$9);2;FALSE)
I'm trying to create a formula that looks for matches in 3 ranges and exports a specific value dependent on if there is a match or not into a new cell.
IE:
If any value in Column A = D1 AND any value in Column B = E2 then enter corresponding value from Column C into F2, if not then place "New Data" in F2.
If it makes it easier to conceptualize: Columns A,B,C are in one worksheet. Columns D,E,F are in another worksheet.
Worksheet 1
Column A Column B Column C
1 Frog Eye Head
2 Rabbit Eye Head
3 Cat Nose Head
4 Horse FR Hoof Leg
5 Dog Nose Head
Worksheet 2
Column A Column B Column C
1 Horse Eye "New Data" (no match from column A or B)
2 Cat Nose Head
3 Dog Nose Head
4 Rabbbit FR Paw "New Data"
5 Horse FR Hoof Leg
I need a Formula for Worksheet 2, Column C that searches Worksheet 1 Columns A & B and places the corresponding match from Column C (if there is a match).
I've tried Lookup and IF/AND formulas to no avail.
I have a four columns, one with created date, one with proposed date, one with a name and one with a dollar amount, I want a formula that will look at the two columns with dates and report back based on the name, the total dollars of each line where the dates are prior to today.
View 1 Replies View Related