Lookup Formula Based On 4 Different Criteria
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
ADVERTISEMENT
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
Jun 6, 2014
I 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?
View 5 Replies
View Related
Apr 29, 2014
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].....
View 8 Replies
View Related
Dec 1, 2007
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.
View 9 Replies
View Related
May 31, 2008
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
View 3 Replies
View Related
Jan 15, 2012
I have the following in sheet 1:
Sheet1 *BCDEFGHIJKL1NameLast DoneMarket Cap (M)PEPrice/NAVHigh, 52 WksLow, 52 WksRevenue GrowthCurrent RatioDebt to EquityROA2AMMB $ * * * 5.76 $ * * * *17,361.71 12.8861.633 $ * * * * * *7.05 $ * * * * * 5.30 10%1.20.42%3CIMB $ * * * 7.27 $ * * * *54,036.27 14.8372.156 $ * * * * * *9.01 $ * * * * * 6.56 3%20.61%4RHBCAP $ * * * 7.31 $ * * * *16,022.91 11.0761.435 $ * * * * *10.40 $ * * * * * 6.53 6%3.20.83%5HLFG $ * * 11.70 $ * * * *12,120.91 7.2581.576 $ * * * * *13.74 $ * * * * * 8.44 4%1.711%
I have the following in sheet 2:
Sheet2 *BCDEFGHIJKL2Parameters**********3Last Done**********4Market Cap (M)**********5PE**********6Price/NAV**********7High, 52 Wks**********8Low, 52 Wks**********9Revenue Growth**********10Current Ratio**********11Debt to Equity**********12ROA**********13***********14StockLast DoneMarket Cap (M)PEPrice/NAVHigh, 52 WksLow, 52 WksRevenue GrowthCurrent RatioDebt to EquityROA15***********
1)I need to create a formula to populate all stocks that satisfies the parameters that I will input in cell C3 to C12 in sheet 2 based on the data in sheet 1.
2)The stocks should then be populated in cell B15 downwards in sheet 2. If there are 20 stocks that satisfies the parameters in cell C3 to C12 in sheet 2, then all 20 stocks should be populated in cell B15 downwards in sheet 2.
3) The respective details of the stocks should also be populated in cell B15 to L15 for all stocks that satisfy the parameters entered in cell C3 to c12 in sheet 2.
View 4 Replies
View Related
Apr 29, 2013
I have 2 sheets.
Sheet one column A has random dates from 1 jan 2013 to present in order base on fuel purchase dates for our company.
sheet one column F has the purchase price i.e 3.98 gallon
Sheet one column A can have a date entry of lets say 2/4/13 with no data in F because no fuel was purchased just the meter was being read.
Then Sheet one column A can have 2/5/13 where column F has 3.78 where fuel was purchased. (these are 500 gallons tanks that are being refilled as needed)
Sheet 2 row B has just the month formatted as Jan 13, Feb 13, Mar 13, .... you can use B1, B2, B3...ECT for the remaining months
What I am looking for is on sheet 2 in cell C1 a way to look at B1 for the month (i.e Jan 13) look at a range of cells on sheet 1 column A for any dates that fall in the month of Jan, then look for data in corresponding F range and if no data is found then use the last months data (Dec 12)
I can make sure I place a dollor figure in the first row of Jan on the sheet, so by default we can use that if no data exist for Feb.
View 9 Replies
View Related
Apr 18, 2007
I have two worksheets, one is setup with a part number only and the months Jan-Dec. The other worksheet has those same part numbers, but it also has the quantity and the months Jan-Dec. I need to do a lookup to find the part number, multiply it by the quantity on that given line, and then multiply it by the quantity in the month. I will show you how my worksheets are setup:
Worksheet1:
Qty P/N Jan
1 123456 100
1 123456 100
2 456789 50
4 123456 25
3 345678 500
1 456789 75
Worksheet2:
P/N Jan
123456 Sum of ALL Jan Demand * the Quantity of each item
345678 Sum of ALL Jan Demand * the Quantity of each item
456789 Sum of ALL Jan Demand * the Quantity of each item
View 9 Replies
View Related
May 28, 2008
See the attached file. I think index and match may help me having done a search several times in these forums, but having been trying to do this for several hours I am just struggling to understand each part of the formula unfortunately. I have a list of data (cellsA1 to C33) which I need to use as my source for the information that is displayed in cell I2 based on the two selections made in cells F2 and G2.
I can't use filter and data sort in this situation - I am creating a spreadsheet for someone who truly knows nothing about excel, so I need this to be as simple as pointing and clicking at cells F2 and G2 and the formula doing all other work. Is it possible to do what I am asking for (hopefully the fact that peoples' names are repeated and months are repeated won't cause a problem?
View 4 Replies
View Related
Apr 5, 2014
show/display data based on month criteria, with selected month in cell H2 (as start date) and I2 (as end date) my expected result start from cell H7, i called "blue area"..
see my attached workbook..
View 9 Replies
View Related
Aug 22, 2008
I have a bit of a challenge. You'll have to take a look at the attached files to understand what I am trying to do. I am asking for your advice. I am creating multiple spreadsheets (QPPcompany.xls) that will refer back to a master data(QPPdata.xls) file.
I am trying to figure out how to best set up the data file so that I can do lookups on the data from the referencing files. Here's the challenge. I need to be able to find in the data page the company name in Column A and then how many L's (or C's or P's) they had in a specified month. So for instance I need to be able to find the number 3 in cell E6 and place it in B15 of the referencing file. The required criteria for lookup are Company Name, Month and then L, C or P.
So my question is, is this possible with how my data file is currently set up? I think it may be a problem to have the month/year in row 4 refer to 3 different colums for each month (L, C and P). I don't mind making changes to the data file to make these lookups possible and I think it may be necessary.
View 14 Replies
View Related
Feb 17, 2009
I'm looking for a formula for return a result only when one other criteria is met. I've looked at SUMPRODUCT and VLOOKUP as options but haven't been able to make them work. Here is what I need:
I want cell E33 to look up cell B33 in 'Test Result Table' and return the 12th column of information, but only when cell C33 and 3rd column in 'Test Result Table' match. If the first result isn't a match I want the lookup to skip it and return the first available match.
I don't want to add anything, I just need a match returned based on C33 and the 3rd column being equal (cell C33 is a VLOOKUP of the 3rd column in 'Test Result Table').
View 4 Replies
View Related
Dec 21, 2011
I am getting an error i have list (sheet4) i want to lookup the data from different table (Name range) based on the location in column A i am using the small function copy down, in cell C6 i want the range ROWS($A$2:B6) go back to ROWS($A$2:B2) since the criteria in column A has changed from China (A5) to US (A6)
Sheet4ABC1Locationproductsamount2ChinaA2954.63ChinaB1565.284ChinaC3772.055ChinaD2027.76US#REF!#REF!
Array FormulasCellFormulaB2 =INDEX(INDIRECT(A2),SMALL(ROW(INDIRECT("2:10"))-ROW($A$2)+2,ROWS($A$2:A2)),1)C2 =INDEX(INDIRECT(A2),SMALL(ROW(INDIRECT("2:10"))-ROW($A$2)+2,ROWS($A$2:B2)),2)B3
[Code].....
View 1 Replies
View Related
Aug 31, 2006
I have an array in excel, 4 columns: Month, Day, Stock, Price. I dont know the most efficient way to handle arrays. I know i can loop through an entire array to find a specific value, but if i have a large array, this doesnt seem efficient.
For example, I would like to find the price in an Array (or Range), when i know the value of two columns. If this were in a database, I would write sql like this: " select Price from TABLE where month = 1 and Day = 3". The result would be 40.21. How can i do this with an array? I have attached a sample spreadsheet with the example mentioned above..
View 3 Replies
View Related
Sep 8, 2006
I have a work book with several work sheets, what I'm trying to do or find a a method of coping data from one sheet to another provided the data in any given cell matches a specific criteria. Example below:
Work sheet 1 is the primary sheet in the work book on this sheet I have a cell with a text value (Bob). Ok on the next work sheet 2 I need to search for a cell that contains (Bob). When or if it is found I need information from other cells in that row (where Bob was found) copied to work sheet 1 in a specified row and colum. If it is not found a negative value can be placed in the specified cells.
View 3 Replies
View Related
May 26, 2009
I have an items list on page 2. On page 1 I want to create a drop down menu in C2 that references the list on page 2. Then when I choose an item, I want the additional information in cells D2 and E2 to come in automatically. The list on page 2 will be expanding daily, and when I expand it I can simply insert a new row so I correctly alphabatize it instead of constantly having to resort it.
With that said....
1. How do you put in the drop down box that references information on a different sheet?
2. What is the best way to reference the information in my ever expanding situation: Index/Match, Vlookup, Lookup, or something I havent mentioned?
View 14 Replies
View Related
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
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
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
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
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
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
Jan 8, 2009
I'm looking to create a macro that will take data from an input sheet, and paste it into a cell in another worksheet, based on criteria specified in sheet1.
Specifically in the attached example, the macro would copy the data in cells C8:C10 of sheet 1, then paste them into sheet 2 based on the data specified in cell B3 i.e. it would paste them into the column headed Mar-09. I intend to make this cell a drop down, so that the user can then select the next reporting month and run the macro again to paste the data into the Apr-09 column.
View 2 Replies
View Related
May 11, 2009
1 . Now i need amount to be written in sheet 1 from taking value from sheet2 based on the code. The code & description column has the same value. but it will be placed in different order
Ex:
the amount should get filled up only for the status yes & no . others it should return null.
View 3 Replies
View Related
Jul 11, 2012
I have encountered a situation where I need to essentially accomplish a reverse Vlookup (using index match) and return multiple values.
View 1 Replies
View Related
Apr 21, 2006
I would like a lookup that takes multiple criteria and that is not an array formula! Unfortunately I decided to use array formulae and my spreadsheet went to over 45mb!! Not good. I've searched the forum for an answer to my questions but couldn't find any! I've attached a spreadsheet as an example. The examples I am using have {Sum(IF)} formulae in it (array) and I would like to change those to others that will not increase the file size so much and will not take too long to calculate.
Basically, I would like a lookup that will return me the Amount Paid and Full Cost based on the person's name and the date. the data and the results table are both on separate sheets. It would be nice to bring that file's size back down to less than 4mb!!
View 7 Replies
View Related
Feb 6, 2012
I have 3 columns of data
Months Date Description Royalty
1 11/1/2010 XTY 5555
2 12/1/2010 xxx 4444
about 100 or more lines of data
Months is the running count of the number of months
Bottom line: Starting at 3/1/2011, I need to know how many months did it take to earn $125,000 (Royalty)
View 3 Replies
View Related
Jul 29, 2006
I have a dataset in Excel with first names, last names, states, cd sales total cost by state... and I am I started a new worksheet cales total sales y state. Now, in this new sheet I want have the states copied over and in the B2 cell I want to write a formula that will enable me to have the total cost from O2:O112 cells ("Total Sales") to be sumed up by each state with absolute references. How would I do this? Example I am currently using =SUMIF(Demographics!F2:F112, "AK", Demographics!O2:O112) which is giving me the correct total sales for AK, but I want to copy this formula using absolute references and have the states change while keeping the fields O2:O112 the same. I just don't want to enter in each state which is what I was starting to do, but It will take a while =SUMIF(Demographics!F2:F112, "IA", Demographics!O2:O112) as an example here is Totals by State! worksheet:
AK $324.50 <---- Total from Demographics! for AK
IA $428.68........................
View 4 Replies
View Related
Dec 26, 2013
I have a sumproduct formula based on some criteria, but I don't know how add another criteria wherein I need to exclude in the count if the date in column F is 1/1/2009
Attached excel file for reference. LE26dec.xlsx
View 5 Replies
View Related
