Nesting Multiple Vlookup Conditions In An If Funciton
Sep 24, 2009
I am not sure if the VLookup or the If function will be the best way to perform this tast but I think it is they way I would want to go.
I have attached a sample workbook of what I am working with.
Please refer to it.
On sheet 1 I named the table array Ear_Tag.
This is information that is provided to me from the farmers.
Note that there are sometimes more then 1 pig with the same tag number. The only way to tell them apart is by the Test Date.
On Sheet 2
Is where I record the test results.
I dont want to have to look up each pig after each test and fill in the rest of the cells manually. I would like to have a look up of the ear tag and the test date to find the correct pig. Then each of the cells will fill in the correct information.
I need a formula that will determine if a line is active or expired by first looking at the category and then looking at the number of days. (While still incorporating the ISBLANK section of the current formula.)
For Example: If the category is either FWW or FWW Ext then the line will expire after 365 days. If not, then it will expire after 180 days.
Trying the following syntax and I do not get any value in the cell if either of the conditions are true. Pretty sure I'm nesting the functions incorrectly. Maybe I should use Index/Match? =IF(AND(E24="SI",F24="SI"),"ER2","",)IF(AND(E24="sn",F24="sn"),"er2","")
I have written a snooker scorebaord spreadsheet which keeps a history of highest scores and highest breaks, but am unable to find a way of showing these key values in a table.
Attached is a cut down version to demonstrate the problem. If you can provide a solution it can be either in formula or code.
I have a table (table1) with material numbers which have a price . This value is time dependent i.e., a material 999 could have a price of $10 for 1/1/2008-1/15/2008 and $20 for 1/16/1008 - 1/31/2008.
A B C D 999 1/1/2008 1/15/2008 $10 999 1/15/2008 1/31/2008 $20 998 2/1/2008 - 2/25/2008 $15
I have another table (table2) in another sheet in the same workbook have a material and date.
A B C 999 1/10/2008 999 1/20/2008 998 2/15/2008
My requirement to take the material value and date in table2 and match it with table1 and get the value of column D in table 1 to column C of table2.
I have tried using vlookup but it only works for the first match and doesn't check for other values
I've attached a sample/equivalent workbook of what I'm working on which will hopefully make it clear(er).
>There are two worksheets/month. Both worksheets (represent 2 different categories) are structured the same, two columns: model code & $ amount. >The model codes change (in # and actual model), between categories and month.
>The data for each month rolls up into a year-to-date summary worksheet, with 4 columns: Model (includes all models YTD, each only listed once), category1 YTD, category 2 YTD, & Total YTD).
Previously this had been done by manually entering any new models for the month into the rows in the YTD summary sheet. And the totals for each model (highlighted in yellow in the YTD tab in my sample) were just done by an adding formula, with the new month's data manually entered into each individual cell at the end of the formula (...+X). I know there's a much better way to do/automate this! (there are a lot more models than I've put in my sample aka it's way too time consuming manually).
My problem is twofold: 1. (main issue) I have been trying to do this using various IF statements nested in VLOOKUPS, and vice versa, but the issue that arises is for models in the summary sheet that don't exist in a given (month's) table. I want the value for those models (for that specific month) to be zero, but I cannot figure out how to get that to work in my formula. The only piece that works for me thus far is =VLOOKUP(A3, 'Jan Cat1'!A2:B18, 2, FALSE), but I've tried nesting it in IF statements, nesting IF statements in it, using ANDs & ORs, no avail.
I'm not even sure any of these options are the best ways to reach what I'm ultimately trying to do. A pivot table may be better? But I will need to keep/preserve the summary sheet for each month (so there cannot just be one big updated master pivot table).
2. If I could find a way to automate/refresh & update the row of models each month, it would be the sprinkles on the icing of this cupcake.
I have three macro's. 1) Execute 2) CreateMatrix 3) CollectDebtCount
What I want to do is have the macro called Execute call up and trigger CollectDebtCount macro and CreateMatrix macro.
My problem is the value to limit the For/Next block in CreateMatrix is not using the value generated by the CollectDebtCount.
An example of the output is as follows: If the user enters the number 3 to the question "How many different type of debt do you own?", then the macro CreateMatrix should generate the list starting in cell A2, the value = 1, then in cell A3, value = 2, and in cell A4, value = 3.
Sub CollectDebtCount() Dim Question Question = InputBox("How many different type of debt do you own?") Trim Question End Sub Sub CreateMatrix() J = 2 I = 1 Dim Count For Count = 1 To Question Cells(J, I).FormulaR1C1 = Count Count = Count + 1 Next.......................................
I had the first and third argument working properly but am running into difficulty when I tried to enter the middle formula.
If A1 + B1 = 0 then "0" OR
If A1 = 0 AND B1 is greater than 0 then give B1's value times .01 If neither of those are true Then A1 minus B1, divide the result by A1 and then multiply it by negative 1.
=IF((A1+B1=0),("0%")*OR,IF((A1=0 and B1>0),(B1*0.01),((A1-B1)/AI*-1)))
I want to accomplish a few things in one cell. The basics are this:
I want to pull the lowest numbers from a given sample. The sample needs to have a time component (10 lowest out of the most recent 20 results). Those numbers are to be averaged. That average is to be multiplied by .96, then that final yield needs to be truncated to 1 decimal.
Now, I'm not sure this is possible or not. But, if it is, I want to also select the amount of numbers I pull from the sample to average to be based on the sample size itself. For example, if there are 10 results in the sample size, I only want to average the lowest 3 of the 10.
Is it possible to formulate all of these rules into one cell?
let me start by saying that I know an example workbook would be useful here, but the part I'm struggling with is the [managementroster.xlsm] file, and there is A. no way I can release it to the internets and B. its so huge/complicated I couldn't even begin to reproduce a portion of it, scrubbed of data, and hope to maintain its functionality in a meaningful manner.
[Code]....
This formula checks a staff number on this spreadsheet, and then goes and looks at the staff number on the roster. Once found, it returns that staff members roster, but changes any manager codes in the MRC list to Mgr, and changes all other roster codes to Free.
I now need this formula, before altering roster codes to Mgr or Free, to only return codes that are a match for another table (or after really. I don't particularly care, so long as only codes are shown that match data from another table). I think an index/match function would do the trick, but this forumula is already at the edge of my excel ability, nesting another function within it is completey beyond me. The relevant cells for the index/match function would be:
This first Match function targets the column. $E3 is the date required, $BA$1:$DN$1 is the range the dates are entered in Match: Lookup value = $E3 Lookup array = '[ManagementRoster.xlsm]Vacancies!'$BA$1:$DN$1 match type = 0
This second Match function targets the row. $A$4 is the department name, $B$434:$B$452 is the range where all departments are entered
Match: Lookup value = $A$4 Lookup Array = '[ManagementRoster.xlsm]Vacancies!'$B$434:$B$452 match type = 0
Index: array = $BA$434:$DN$452
So I think my final function is
[Code] .....
But I have absolutely NO idea where it would fit within my first formula, or how to code it so that my original formula only reproduces results that are found in both sheets, or anything.
My challenge is pulling in a data element from a file to an exisiting file based on two criteria. Example: give me the value in cell e1 if cell b1 matches AND
File 1 A B C 1 H12377 03/05/2013 123 2 H12377 03/27/2013 276 3 H32389 05/03/2013 335
File 2 H12377 03/27/2013 _________
how do I get C2 value from file 1 (276) into file2 since column A is not unique but column A plus Column B is unique?
I have found that excel can be the best thing ever and also a nightmare when you are stuck on something. I really apprecciat anyone who can help me out.
In an excel workbook i have a sheet that has rows and rows of Sales Leads.
In column A i put a letter "C" if they have turned into a client or an "X" if the sale was not made.
I am looking for a function that will look at column A and when it sees the letter "C" then copy the entire row across and put the row into sheet 2.....
I am trying to retrieve data from another file using the VLOOKUP function however this is only to happen depending on if any of the 3 items of data appear in column 8(H)
OLY OLY - QUO OLY - PRO
I have the following and know this is not correct
Sub BlockAllocationsVlookupAll() Dim x As Long For x = 1 To 65536 If InStr(1, Sheet1.Range("$H$" & x), "OLY") > 0 Then Sheet1.Range("$I$" & x) = Sheet1.Range("$I$" & x) & "sometext" End If Next End Sub
I know the above doesn't do exactly what needs to be edited to include the Vlookup below
I've got no clue about all this, but I've had to get specific formula examples and fill in the blanks in order for my timesheet to work. There's just one final problem if somebody could please help.
This is a timesheet for a 5 day work week. I need to count the number of unique log numbers for a specific activity. The log numbers counted must be unique across the entire week, not just for each day, which means I want the formula to count the unique log numbers across multiple sheets.
The formula also has multiple conditions. I got 2 columns. The first part of the formula needs to verify a word, say, "split" and if it does it checks the adjacent cell for a unique log number. If both arguments are true, it counts the log as 1 unit.
Here is a working formula for only one page. =COUNT(IF(D4:D29="split",IF(FREQUENCY(C4:C28,C4:C28)>0,1,)))
Here's 2 problems with this formula: 1. I will count if it encounters a blank cell in the Log numbers the first time (which will happen as not every activity we do has a log#), but it will stop counting if it encounters a second blank cell.
2. I don't know how to make it work across several sheets.
This is an alternate formula which works and skips the blank cells, but I don't know how to add the multiple condition of "split" and to have it work across multiple sheets. I just copied it Microsoft. As I said, I don't understand it, I just fill in the blanks.
D E I J Account1 Sum1 Account2 Sum2 151 93,79 BF2479 30,00 1476757 93,79 BF7 22,00 BF2479 30,00 151 93,79 86041 84,03 23235 30,00 534 6608,45
2. I need to clean the multiple identical data in the list by both Account no. and Sum (of course the list is much longer!) so that the list should look like this:
D E I J Account1 Sum1 Account2 Sum2
1476757 93,79 BF7 22,00
86041 84,03 23235 30,00 534 6608,45
I must say that I am a beginner in Excel, so I tried to use this:
(which is reverse of the first one) for column J; (The ranges are the ones used in the real sheet), not for the ones in the example above!); The formula does seem to eliminate a dew unwanted data, but does not give me all the wanted results!
I'm trying to divide my data into 6 different groups, based on 2 different criteria. First, I am not sure how to write the logical test to take 2 columns of data into consideration (using "&" and "AND" do not work; I am not sure what else to try), and second I can't figure out how to write the formula so that it can select from 1 of 6 conditions.
So, overall here is what I want:
If DL2=3 and CK2=1, then I want this to be labeled as '1' If DL2=3 and CK2=2, 2 If DL2=1 and CK2=2, 3 If DL2=2 and CK2=2, 4 If DL2=1 and CK2=1, 5 If DL2=2 and CK2=1, 6
These 6 conditions cover all possible combinations of numbers in the two columns.
X Y Z DOU Quarter Sum 0 Q1 = sum of 1's in Q1 1 Q1 = sum of 1's in Q2 1 Q2 = sum of 1's in Q3 0 Q2 = sum of 1's in Q4 1 Q3 1 Q1 1 Q4 0 Q2
A cell in the SUM column must contain the sum of 1's in column DOU if under column Quarter the value is Q1. Similarly for Q2, Q3 & Q4 if only the corresponding value under DOU is 1.
Its like.. count the number of 1's per quarter and come up with the sum in 4 distinct cells for each quarter.. i.e. quarter-wise sum/count of 1s
If ActiveCell.Value < -25 And ActiveCell.Offset(-2, 0).Value < -25 And ActiveCell.Offset(-3, 0).Value < -25 Then Range("C4").Select End If End Sub
I am looking to do something similar on excel 07 as follows: Range("O16").Select IF ActiveCell.Offset(0,-1)Value ="1" And ActiveCell.Offset(0,-2).Value = "1" Then ActiveCell.Value = "1"
Compile error expected: Then or GoTo At the First value (which I have highlighted in red)
Also would like the If formula to repeat for range O16:O36.
I need to create a conditional sum in the report tab in the yellow cells that will sum all the values from the list sheet, based on the following conditions.
1) The name is in the list 2) The code in the list is one from the codes tab.
I saw somewhere that I need to put an 'OR' statement in the conditional sum but I can't get it work. It seems to add up values for the code 'HOLIDAY' and I don't want it to.
I cant seem to rack my brain around how to go about making a formula in order to achieve my desired effect using the IF Formula. The data is as follows:
Worker Status % S1 75% and higher S2 42.8571% -74.9999% SH Anything below 42.8571%
My sheet pulls the workers status from a preset data table, lets say S2 for Joe. I want the cell beside his status to compare what his percentage is and display what the status should be (based on the conditions of the above table). If hes working 73% it will turn up S2 and I can compare the value from the table and the value generated based on his working percentage (I would use conditional formatting to color the cell red or green to indicate if it matches or not.)
Column A: Individual Column B: Not used in formula Column C: Not used in formula Column D: Location Column E: Duration in Minutes Column F: Pay (Column E)*(Column G) Column G: Formula
(simplified version listed below)
[Code].....
To be clear of what I'm trying to achieve:
When in location 1 & 2 (Column D), the pay for each individual is a different rate depending on the duration in Column E, but when in Location 3, the rate is consistent no matter what the duration.
My question is:
How do account for individuals who aren't individual1? That is, my understanding of the above formula is that I need to list each individual that will appear in Column A for it to work. What if I just want to apply the above to individual1, but apply some other calculations to all other individuals?
I had tried adding these lines (with their corresponding closed-parentheses at the end)
[Code]....
But it doesn't work. I'm guessing it's some sort of OR, but I don't yet grasp how.
I should note that my formulas are all on one line in my spreadsheet, I just have them on separate lines to make it easier to find my way around.
I'm trying to get a sum only if 2 conditions are met.
Column A has dates (ex. 01/02/2007) in a random order. Column B has a location in a text format (ex. London). Column C has a series of values formatted as [h]:mm.
What I'm trying to do is get a sum of the values in column C where the corresponding values in columns A and B = the date and loction I specify. I've tried...
I have the following formula entered into cell O4: =IF((AND($K4="1",$B4="1 - C",$I4="open")),(1),(0)). When the three conditions are met in the corresponding cells, I get a "0" returned in cell O4. I would like the formula to return a "1" when all three conditions are met.
I want cell B to be blank if 1 of 2 different conditions are met. If Cell A is blank then so too should be Cell B. If cell A contains data, then I want cell B to perform a formula $D$1-E6.
However, if Cell C contains data then Cell B should return to a blank status.
