I am trying to use the formula below with array brackets around it and it does not seem to be working?
=IF(ISNA( LOOKUP(3,1/((('000'!$D$2:$D$500=A5)*('000'!$F$2:$F$500=E5))*('000'!$H$2:$H$500=B5)),'000'!$C$2:$C$500)),"NOT YET",LOOKUP(3,1/((('000'!$D$2:$D$500=A5)*('000'!$F$2:$F$500=E5))*('000'!$H$2:$H$500=B5)),'000'!$C$2:$C$500))
I 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 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 Replies View RelatedOn "SHEET1" loop through column "MODELCD" and perform a vlookup of its line items on column "NAME" in "SHEET2";. Once it finds a match on "SHEET2", copy the data in all 5 cells to the right of it and paste it back into "SHEET1" (in the cells to the right of the "MODELCD" line item being looked up).
"
I have attached a workbook below to help illustrate my task. What I'm trying to avoid is writing "10" separate Vlookups, when I could capture all the data in one procedure. I have been trying to figure this out for a couple of hours and decided to see if someone else knew how to do it. I have the idea in my head on how to do it, but struggle to translate it into working code.
Im open to using a FIND or SEARCH method instead of a VLOOKUP, but I was just more familiar with vlookups. This code i will be running on about 1000 line item, if that dictates one method vs the other.
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 three columns.
For instance, Column A are names. Column B are totals points and Column C is year.
I want to a cell too look up all names of "John" for the year 2007, then with those two conditions add up all total points for John in 2007.
Any way to look up any cells with in a data dump (example: PR1420829-V2) but not having to include the 'V2' but still picking up the cell.
Im currently using the fomula;
=IF(ISNA(LOOKUP($D$5:$D$1000,Test!$C:$C,Test!$L$1:$L$381)),"",LOOKUP($D$5:$D$1000,Test!$C:$C,Test!$L$1:$L$381))
I would like to lookup and return results based on the values of two cells A1 and B1.
The returned results are from column G to column I and always returned in the same location of the spread sheet.
For example if P2560 is selected in A1 and D10 in cell B1 then after lookup the results would be the values of G3 to I4. Alternatively if A1 is P2560 and B1 is D20, then the results are the values of E5 to I7 and so on.
see the attached spreadsheet for more details. This shows the main data (A2 to I15) and the expected results depending what value is selected for cells A1 and B1.
I have 40 or so case studies on one worksheet. Each case has four columns- the first is the year, and the next three are values for that year. Each study is 140 years long, but they don't all start in the same year. What I want to do is pluck all the values from the fourth column of each study, and total them up for each year.
View 9 Replies View RelatedI have one sheet that is used to check off when certain questions are answered for an inspection of departments in a building. As the first image shows, Row 1 contains the questions; Column A contains the departments. I simplified this for the image, but the rows represent individual audits, over time, so a department will appear again and again on subsequent rows, as new audits are conducted. When it's time to do a new audit, the user starts a new row and selects the department in Column A from a drop down list (via data validation).
What I need to happen is when the user selects a department, certain cells under each question are filled with black, indicating that the question does not apply to that department.
I have used this handy formula
=INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),ROWS(A$1:A2))
to create an index page that looks like this right now:
Index
35035-9
37032-14
37065-6
37079-4
Now what I would like to do is get cell B1 on each of these sheets to post in the next column without hardcoding the name of the sheet into the formula. So in the cell next to 35035-9, I would like the formula to look at worksheet name, and then go to that worksheet, and return cell B1. Same for all the other worksheet names.
There will be multiple additions of worksheet to this list over the course of the next year and I want to be able to index them easily.
I have a table in one sheet as follows: .....
View 9 Replies View RelatedCan they be used to reference data based on data inserted into more than one cell? I have attached an example as its kind of confusing to explain.
View 14 Replies View RelatedI have a formula I need to modify.
In column b I have a list of items by number, each one is unique
In column c I have a list of items by number. In this column a item may not be listed or listed 1000's of times.
In column d I have a list dates for each of the items listed in column c
In column e I have a list of prices for each of the items listed in in column c
The idea with the orginal formula was to look up the item and return the date
=MAX(INDEX(($C$2:$C$39999=B2)*(D$2:D$39999), 0))
Now I need to get it to return the most recent date from column d and the dollar amount right next to it from column e. How would I do that?
I trying to return the most recent date and price for a item.
I thought about breaking this into two formulas and having the second formula read the date that was returned from the above formula and the item number from column b and returning the value from e, but got stuck. I was thinking a lookup function with two criteria.
I have a spreadsheet that I am trying to make more functional. Basically, when I click on my dropdown (sheet1), it changes the value of the cell in B3 in sheet2. Also in sheet 2, I have column and row heading where I would like to return data via this lookup.
Rows = customers
Columns = product groups
So, you click on the drop down and it changes the month's data that you want to see (at least that's the theory). On sheet3, I have the sales data by customer, by product group and by month. If it weren't for the month, a simple index/match combination would work. How can I get that same functionality, to also look at the month?
I have a XL work book with different worksheets what i need to do is compare cell a2 and e2 of worksheet 1 with all the data on sheet 2 and then return the data from the same row but in column m I dont know if this makes sense but it pretty much is the same as a VLookup but comparing 2 cells instead of one
View 9 Replies View RelatedI have following sample list of data:
PostalCodesCountryTerminal
0000 - 1069BEGrimb
1100 - 1179BEGrimb
1070 - 1099BETern
1020 - 1229ATLZN
I now need to determine the Terminal for postalcode 1086 in BE
but since the postal code that I need to find is within a range
(1070-1099) that is within a single cell, and there is a second
argument (BE) in the next column, my novice knowledge of VLOOKUP()
isn't sufficient to come to a result
I am trying to enter a value in cell F1 by comparing the values in cells A1,B1 & C1. By using 'if, and, or' functions I can automatically input a figure for a limited number of values in A1 to C1. What I need to do is lookup from a database for say 10 possibilities in A1, five in B1 and 35 in C1 returning the correct number for the combination in F1.
View 4 Replies View Related621
532
249
I have these numbers located in column A (rows 1:3)
formula that will return my search result (in these case 532) as well as the information immediately above & below the location of cell that has my search info?
I have 40 or so case studies on one worksheet. Each case has four columns- the first is the year, and the next three are values for that year. Each study is 140 years long, but they don't all start in the same year. What I want to do is pluck all the values from the fourth column of each study, and total them up for each year.
Here's how the sheet is set up- row 6 is a header row with four repeating column heads- year, value1, value2, value3. So year is in col A, E, I, M, Q, etc. up to FM. Value3, the one that I want totaled is in col D, H, L, P, etc up to FP. Each year column begins with a different year, (with some repeats) but each is 141 rows long, so that the first study begins in 1994 and ends in 2134, the second is from 1985-2125, the third is from 1991-2131, etc. I want excel to find all the value3's from 2007 and total them, all the value3's from 2008 and total those, up through 2145. The data range is A7:FP147. I tried adjusting some of the suggestions, but no luck. I was thinking I'd have column FQ with all the years 2007 on, and FR by the total of all the value3's from that year.
I'm looking to look up sheet name based on a date, then look the date up within that sheet which is a merged cell, then return the figure in the corresponding cell.
I have a sample worksheet to attach, just have to work out how to do it.!
In the sheet "Cashflow Summary" in cell E24, i want to lookup the sheet name based on the value in C22 (merged cell), then lookup the date in that sheet (6 August 2014), and return the value in cell y8.
The difficulty is with firstly the merged cells, but also performing the lookup over a number of different arrays, as the sheets for each month, have the months listed under each other in rows of 5 days at a time.
I am trying to create an automated receipt for a list of artists. I have given the artist an individual reference so I am doing a VLOOKUP to return description/price value etc but I can only get it to return the initial value and I have approx 10-15 works under each reference. Maybe VLOOKUP is the wrong formula to use.. I have attached the file.
View 5 Replies View RelatedI have a workbook with two sheets 'Driver details' and 'Jan Raw Data'.
If column B Employee (Jan raw Data) matches the names in Columns B&C (Cloumn B & C, Sname, Fname) on the 'Driver details'.
If it equals a certain name i.e. Jon smith, then in empty column E of the 'Jan raw data' then copy the entire row of that individual from the 'driver details'.
Driver details
A B C D E F
Emp No Sname Fname Desc Desc Reg
123 smith Jon opticar BVA MA2
456 pratt mike opticar TMA YP7
Jan Raw Data
A B C D
Occup Emp Product Supp no.
Crawley Smith Jon unleaded 3453534
Crawley prat mike unleaded 89797
I 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.
Actually i can do it just by using LOOKUP formula but the mark scheme says LOOKUP and IF.
If i use Just LOOKUP i should do the same thing for other cells but i want to generate a formula and replicate it through all the cells.
Here is the question;
Enter formulae in cells C2:C10 that refer to Book Orders.CSV and display whether the Text Book is Required by the student whose Student id is entered in cell A2.
I also uploaded the source file. Q3.xlsx
I've a question. For instance
apple,lemon,strawberry
fruit
green,blue,white,black,grey
color
...
...
...
...
I've a list like above. How can we use lookup formulas from comma-separated cells?
white> color
lemon> fruit
in the lookup function listed below, it only produces a result for the first exact macth. well, what if the data i am searching on could result in multiple results?
i would like advise on how to modify this formula to bring back the 2nd, 3rd, 4th, etc instance of a match in the lookup array.
the original formula , i found on this site, allows you to combine multiple cells to create a unique reference, and then search in another table containing columns of those combined cells, and results in the intersection of a row and column and that is the data you want returned ....
kind of like a hlookup, but you can use one or more cells to create the unique lookup, and bring back whatever column you want (where as a hlookup is limited to searching the left most column and bring back a column to the left)
here is the formula:
{=INDEX(data to retrieve,MATCH(cell1&cell2,table to find cell1 in&table to find cell2 in,0))}
"data to retrieve" is an array of one column containing the data you want to retrieve
"cell1&cell2" is a combination of cells to make the unique lookup (this could just be one cell if you wanted)
"table to find cell1 in&table to find cell2 in" is a table array to look in to find the look up value
I am trying to add up wins and loses for individuals on a team. 6 players
There are 3 games per match and one match a week for 15 weeks. One sheet per week.
The players are inputted randomly each week. And the scores are entered after the match.
The following is a part of the formula that I am using but it returns a #NA because the cells in the weeks not played yet are blank, players and scores. Is there a better way to do this or a way I can ignore the blank cells until they are entered. Thanks in advance.
=SUM(LOOKUP($A$17,'Week 1'!$C$20:$D$23,'Week 1'!$E$20:$E$23),LOOKUP($A$17,'Week 2'!$C$20:$D$23,'Week 2'!$E$20:$E$23),etc.)
A17 is the players name, 1st range is where the players name would be entered, 2nd range is where the score is entered.
I am given a number say 155 and want to find out which range the number falls in. For example there are ranges like 142-151,152-161, and 162-171. I need a function that tells me my number 155 belongs to the labeled range 152-161.
View 9 Replies View RelatedI want our sales people to be able to select from the list of products we manufacture and have they corresponding information automatically populate. For example, if the salesperson is trying to put a quote together for 500 lbs. of Stainless Steel nails, they can select the correct size and have the list price, cost, margin (etc...) fill in the adjacent cells automatically.
View 5 Replies View Related