Formula To Lookup Multi-criteria Information
Dec 20, 2013
I am using a list like this.
A
B
C
D
1
Teacher
Name
Color
Size
[Code] ....
I need formulas that can evaluate the table above and provide the information below. If there needs to be multiple steps/formulas, I'm okay with that.
Two sticking points, BOLD - be listed twice with the same teacher. I don't want them counted twice in the "Size 3" column. BOLD & italicized- same student could be in two different teachers' classes. They need to be counted under both.
Teacher
Red
Green
Blue
Size 3
[Code] ...........
View 5 Replies
ADVERTISEMENT
Mar 20, 2014
What formula should I put in NUTRISTATUS column. That will search value in the table of MALE BMI and FEMALE BMI. for example when I input data M(male) in ***(gender), age in M(m0nth) and BMI it will search in MALE BMI table..
and I input data F(female) in ***(gender), age inM(month) and BMI it will serach in FEMALE BMI table.
and if the criteria match will appear the word SEVERLY UNDERWEIGHT or OVERWEIGHT or UNDERWEIGHT or NORMAL or OBESE in NUTRISTATUS Column..
View 3 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
Dec 11, 2007
I consider myself a beginning intermediate Excel user and am really learning a lot through this forum but have not run accross the answer to what I'm trying to achieve. I would like to return the value in the "Temp" column that meets all the conditions of the columns "letter", "day", and "time." Specifically, the "Temp" of "A", "Weekday", "East." I have attached a small spreadsheet. It seems Vlookup is the approach to use in combination with multiple if conditions or an array. But I am not yet comfortable with such a complex equation. Can someone provide me with some guidance or an equation that would work. So far this is how I've been learning. Looking at equations and deciphering how they are written.
View 5 Replies
View Related
Aug 22, 2009
I have previously been performing a multi level SUMIF of named ranges with a large data range.
This works but its inefficient as it would be better to preselect a subset range before the SUMIF begins matching criteria.
Basically, i'd like to select a range of rows which will parse into the SUMIF formula.
I have solved this by referencing an external cell to create the range.
View 6 Replies
View Related
Aug 1, 2014
If I have 2 columns (A with data to be extracted, B for output). And 11,000 rows of data. How do I take information from column A and out to column B. For example, I want to identify characters in Column A, for example I want to identify "A" "#" and "SQ" to output. For example If column A, has "A" then Invitation, If column A has "#" then Business, if column A has "SQ" then square
Column (a)
A10
extract to
Column (b)
Invitations
Column (a)
#
extract to
Column (b)
Business
Column (a)
SQ
extract to
Column (b)
Square
View 5 Replies
View Related
Oct 24, 2013
I need to change the value returned in one cell based on the criteria of a 2nd cell. In short, if the letter 'C' is entered into A1 and B1 contains a vowel the lookup formula needs to return the value found in column C of a separate spreadsheet, but if the 'C' is entered into A1 and B1 contains a consonant then return the value of Column B. I want this to be applied to C, O, R & S only. If A1 contains any other letter, than the lookup formula should return only values found in column B of the separate spreadsheet.
View 3 Replies
View Related
Jan 19, 2012
I have a report to fill that looks like this:
Table 1:
HEMANITOBAMANITOBA SK12
I want to fill it up with data from this pivot table:
HEIntervalManitobaManitoba SK11-10522-8423-8224-6125-5826-6427-6828-5929-53210-47211-53212-63221-7522-8023-7924-7825-7726-7627-7728-8229-86210-86211-86212-722
So basically what I want to do is for example from Table 1: If I want to find the what goes in cell in B2 (1, Manitoba) then I want to go to the pivot table find 1 which is in the first column(HE), there are 12 intervals to each HE(as seen in column 12), I want to take the average of all the HE with 1 corresponding to Manitoba (the values in Column 3) and to be put into B2 in Table 1.
View 2 Replies
View Related
Mar 26, 2013
I need to know if its possible, and if it is, what the formula would be to get the correct "Rate" to pull based on the criteria given:
User would input the following information:
Zip Code: 56559
Pallets: 3
Weight: 1200
The formula needs to use the following table to use the criteria listed above, to fine the correct "Rate". The "Zip Code" and "Weight" both need to fall between the correct ranges and then match the "Pallet" to find the correct "Rate".
From Zip Code
To Zip Code
Pallets
Weight From
Weight To
[Code]......
View 1 Replies
View Related
Jul 31, 2008
In the attached file I managed to sum up the Quantities per Material & Category. The Original DATA is presented in columns A:E. The requested results/Formulas are at Cells B2:B4. My Question is very simple: Is there a way to sum up the Quantities WITHOUT the Help-Column G !? If so - how ?
View 9 Replies
View Related
May 6, 2014
I have a schedule that covers 3 shifts for the entire month. I have another sheet that is used for crew sign in that I want to auto populate with all the people on that shift for that day.
I need to be able to use 2 different cells on the sign in sheet for reference for the formula (one for the day, and the other for the shift. ex. Day 3rd, shift D). I'm wanting to use an array formula to accomplish this. I've uploaded a stripped schedule as this is for the military and can't have any data on it that pertains to what or who it's for.
BTW our current method is to due it manually for every shift for every day that wastes about 12-16 man hours every week. I have excel 2010 at work currently. Access is out of the question and Macro's/vba may or may not work as security is always being increased on our systems so either a single or multiple formula is the route I'm looking to do that will work the the next few years I hope.
View 6 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
Feb 26, 2012
I'm trying to find a formula that will find an original entry using 4 criteria original entry is cases ordered.
columns A,B,C,D will have to match then give me a result in column E to find original entry if there is one otherwise I will have to enter new row and original entry for cases ordered.
Eg.
A = section
B = description
C = size
D = region
E = cases ordered
Row 2 = fruit, apples, medium, north, 25
Row 45 = fruit, apples, medium, north,
View 3 Replies
View Related
Jun 16, 2008
I am currently using the formula below to lookup a name and its corresponding code. I type the code into cell A13. The name is fixed at B1. This formula then finds the name (B1) in a table and matches the column header of the code which I type into A13 and returns a value. This allows me to type in different codes and quickly see the value corresponding to the name. I also want to be able to type the coding into A13 and be able to see a listing of all of the names attached to that coding.
VLOOKUP(BETA!B1,MAINTABLE,MATCH(BETA!$A13,MAIN!1:1,0),FALSE)
View 9 Replies
View Related
Oct 24, 2007
I'm looking for a formula to pull the correct piece of data from one worksheet and display it in another. I have tried VLOOKUP, but it is only returning values from the first instance of my lookup value. Below I have included a sample of what the data from the first sheet looks like. My lookup value is entered on Sheet2 in cell A1. The formula I have used is as follows...
=VLOOKUP(A1,Sheet1!A2:D13,4,FALSE)
The result this would return for a value of 111111111 is 15, but I'm looking for one more level of depth in this lookup, by being able to pull not necessarily the cost of the first code, but of a specific code......
View 3 Replies
View Related
Dec 8, 2007
I have a rather complicated question, complicated for me. I have one workbook with 24 worksheets. The first three worksheets are names of people, ie kim smith, jack white, bill blue. The next 23 sheets are held in reserve for new people and are currently labeled isr 4-23.
I have used your site to get to a point where I can generate a cell to reflect the tab name. I have used these formulas to accomplish that:
=CELL("filename",'ISR 4'!$A$1)
="'"&MID(F6, FIND("]",F6)+1,256)&"'!"
This generates 'ISR 4'! in the destination cell. I have taken this list of sheet names and crated a dropdown list. Now I would like to apply two tests to this list as well as another list.
SheetnamesWeekofYearHits
Depending on who is picked in the sheetname list and depending on what week is picked in the week list I want to go to the sheetname tab, that week and grab cell b1 or b2 (week 1 b1, week 2 b2 and so on for 52 weeks)
View 9 Replies
View Related
Jun 12, 2014
I make budgets that have line items that have standard price based on 2 factors: a description, and a number. I want to keep these items in 2 different cells. I also have different clients, and the costs are different. I could simplify so that I can use a VLOOKUP with HLOOKUP and separate the clients but I would prefer not to.
Screen shot 2014-06-12 at 12.36.47 PM.png
View 1 Replies
View Related
Apr 27, 2012
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.
View 9 Replies
View Related
Sep 4, 2008
a lookup and match of two values.
For example, in Sheet1 I have a unique transaction reference number and a £ value.
eg. 60231568 £38.05
In the next sheet (Sheet 2), I have a summary of data, and I need to return the company name...
60231568 £38.04 Company X
60231568 £15.12 Company Y
60231568 £10.11 Company Z
60245871 £105.11 Company Q
The look up needs to match trans no, amount (within 3 pence threshold) and return in this example company X.
View 9 Replies
View Related
Dec 18, 2008
I'm looking for a formula (VBA I'm assuming) that will help me create a unique customer ID out of data that my website generates in order to import records into my accounting system.
I have a current list of customers in CSV format with the columns: CustomerID, CustomerName, CustomerZip
Each customer in our accounting system is assigned a unique,7 digit CustomerID in the format of XXX#### where XXX are the first 3 letters of their last name, and #### is a 4 digit number (with leading 0's) to create unique ID's for customer with the same first 3 characters of their last name. SAMPLE LIST:
SCH0001, Lindsey Schubert, 75230
SCH0002, Thomas Schoembs, 53132
ADA0001, Samantha Adams, 28205
...
What I'd like to do is pass the formula 3 parameters (Cust_First_Name, Cust_Last_Name, Zip) and have it parse the .CSV file and either return an existing customer's current ID or generate the appropriate new, unique ID, making sure in increase the 4 digit # accordingly and insert leading 0's if necessary.
Another caveat, if possible to work with, is the ability to also pass the formula another range of cells to append to the end of the .CSV file's data for comparison reasons. There are times when I'll bulk-import orders (or we receive numerous in the same batch) and the potential exists to have two customers that would have the same CustomerID created using JUST the .CSV data. Ie. If we use the example above and have new customers of Steve Schwab and Julie Schwitzer - we'd end up incorrectly assigning them both SCH0003, where if we'd read Steve Schwab's newly created info and customer ID of SCH0003, then Schwitzer would correctly be assigned SCH0004.
View 3 Replies
View Related
Dec 13, 2006
i just want to use vlookup and hlookup to give me the row and column headings for 30 to 40 sesor at a time.... but i keep getting #n/a whenever the functions meet a table of more than one column! (reduced workbook attached)
it should surely be simple to get this data - but i've struggled to no avail. I thought that having the four separate worksheets was the problem - but i haven't had any look even when i dump the data into a single worksheet!
i basically just want excel to return the cell reference of a sensor number which exists in a table. sometimes a sensor can appear more than once, but its not very common and i could happily work around that by doing the manual search (ctrl f, find all).
any advice would be very much appreciated, i'm struggling and the number of sensors i need to test will increase in the coming months.. please help!
should i even be using vlookup and hlookup? ive tried all the other excel functions, but they don't seem to be useful?
View 8 Replies
View Related
Oct 24, 2007
I have a sheet that has to look up value on a report from a sheet sheet that has more than value. Ex)
On the report I have values 123A and 1234A
On the data tab I have table that has.
Ref Tot Value Desc Value1 Value2 Value3
1 123A Widgets 1 2 3
2 123A Widgets 4 5 6
Ref Tot Value Desc Value1 Value2 Value3
1 123B Nuts 7 8 9
5 123B Nuts 1 3 5
Ref Tot Value Desc Value1 Value2 Value3
7 1234A Bolts 2 4 6
11 1234A Bolts 3 5 8
So the report need find the values for Value 1, 2, 3 on where the value matches the data tab.
View 14 Replies
View Related
Apr 5, 2013
I'm trying to build a formula on the matching text as below, im getting an value error, however when i press the FX key it shows the correct result.
Column A
Column B
Column C
Column D
[Code] ....
( I would like to match the below two criteria with the Row 1 and Row 2)
Co1
#VALUE!
Latest Forecast
Formula-->
IF(MATCH(A6&A7,B1:I1&B2:I2,0),"Ok","Not ok")
View 1 Replies
View Related
Aug 4, 2009
I have three reference cells (a1, a2, a3).
Below, starting from b4 to l4 I have years from 2005 to 2015, row 5 for the same range contains data, row 6 (c to l) contains a growth rate.
I need to come up with a formula that can multiply (x * y * z *... [product-like formula]) the growth rates for all the years where the current year (row 4) is bigger than reference the reference year in a1 or smaller than reference year in a2. A3 is then used to multiply that total.
View 6 Replies
View Related
Feb 12, 2010
I have attached a sample of my data. What I am looking for is the best (most elegant solution) to my problem.
I am trying to create a macro where I have 2 sheets. Sheet 1 will contain my data, sheet 2 will contain a single column of criteria to search through sheet 1.
I would like for the macro when it finds that criteria, it will delete all rows of data that is tied to that ORDER_NUM
Schedule
EFGHI1ORDER_NUMAREADL_DATESHIP_CODEPART2113510395CANADA EMPACT20100108IN445N3113510395CANADA EMPACT20100108IHT9534113510395CANADA EMPACT20100108ID981C5115436953CANADA EMPACT20100110IPMDMN6115436953CANADA EMPACT20100110IN246F7116292017CANADA EMPACT20100111IY1G0M8116411203CANADA EMPACT20100111IT571R9119169642CANADA EMPACT20100113I9M62C10119169642CANADA EMPACT20100113IT272C
For example, it my criteria was part N445N, I would like for it to delete all rows of data that is tied to that ORDER_NUM (113510395).
Desired Output
Schedule
EFGHI1ORDER_NUMAREADL_DATESHIP_CODEPART2115436953CANADA EMPACT20100110IPMDMN3115436953CANADA EMPACT20100110IN246F4116292017CANADA EMPACT20100111IY1G0M5116411203CANADA EMPACT20100111IT571R6119169642CANADA EMPACT20100113I9M62C7119169642CANADA EMPACT20100113IT272C
View 9 Replies
View Related
Aug 21, 2007
I am using the following piece of code to 'link' a value from a cell in one workbook into another.
ActiveCell.FormulaR1C1 ="='[filepath]Front Sheet FIT'!r7c4"
However, i have come across a cell which displays the result of a reference to another cell (something simple like =b32). Normally I would just link to b32, however, the macro has to be applied to a large number of documents and in some cases text has been specifically entered, while in others the reference has been used. Is there any way of using VBA to display either the result of a formula (if there is one) or text if this is the case?
View 3 Replies
View Related
Jul 9, 2008
Im looking for a formula that will look at the the products in column A and see if their coresponding value in in Column I is equal to "website"
I would like it to count how many website sales the product has and return the product name in order of number of website sales into the top ten list at the bottom.
I have attached a test file which should clear things up
View 9 Replies
View Related
Oct 27, 2012
Need to rank by score1 in descending order and Serial # in ascending order. My biggest problem is to get the rank number to be consecutive. I have listed the rank I would like to see.
Score1
Serial #
Rank
90
146168
1
[Code] .....
View 5 Replies
View Related
Dec 30, 2008
I have a large worksheet that lists employees and the hours they work each day in a pay period.
For Example:
Column A holds a date and Column E holds the number of hours worked for a paytype. So i want to create some function that sums the numbers in column E, but only those where column A holds a date.
Then i want to return that number to a cell, say C20. I think I've managed to tell the macro which rows i want to return values from, but I can't figure out how to say "add these numbers together and put the total in C20."
I'll check this frequently if clarification is needed.
View 9 Replies
View Related
May 12, 2006
Trying to count the number of fields that meet a criteria base in 3 different columns. Worked on this for few hours but still keep hitting the wall of no right answer. I have attached an example sheet.
Trying to find the Number of fields that are not blank in the range of B9:B28 and have no date (are blank) in range(C9:C28) and have a number that is < 7 in range (D9:D28).
View 3 Replies
View Related
