If Formula To Combine Two Lookup Conditions?
May 23, 2014Combine two lookup/IF formulas:
[Code]......
So if formula 1 conditions aren't met then it by default would result in formula 2 conditions being met.
ADVERTISEMENT
Simple Formula For Lookup With Two Conditions And Duplicates
Sep 16, 2013I have two spreadsheets of data, one column contains an employees name, the other is a value.
Employee Amount
Bob 10
Frank 12
Katie 14
Katie 55
Simon 5.2
I would like to do a lookup that states if name is Katie and value is 55 and is in sheet 1, return "Yes", else return "No"
The name and value conditions will read off the other spreadsheet.
Combine 2 Rows Of Data If Conditions Are Met
Jan 17, 2013combining 2 rows of data if certain conditions are met. I have attached an example of my worksheet for reference.
Example.xlsx
What I want to happen is if a number in column J is followed by an "x" then the values in columns AQ and AR should be combined with the row of the same number (e.g. if 236x is in column J2 then the values in AQ2 and AR2 should be combined with AQ3 and AR3 because 236 appears in column J3, the same process needs to be repeated for 237x with 237 etc.).
Combine Data From Multiple Sheets With Conditions
Dec 27, 2012What I would like to do is combine all data (from sheet: Page1,Page2, and Page3) into one sheet named 'Consolidated'. Unfortunately, as you will see from the attached file, my programming and understanding of VBA programming is pretty naive.
What I am after is, when I combine the data, from all sheets into the one, the data should be
1. Automatically Sorted by Patient Name
2. Group, results of the same patient from all sheets one below the other in seperate rows
3. Delete any other rows that may have a patient name and ID, but rest of the rows (New Mole,Clinically Suspicious......Benign Naevus) are blank. (This happens because of the way I have designed my userform. For first visit the details are entered in Page 1, then for second Visit Details are entered in Page2, and for third visit in Page 3. So when I enter the first visit details, the Patient Name and Patient Number are copied into Page 2 and Page 3 even though the other details remain blank. I have done it this way because, if say the Patient comes for visit the second time, the clinician may or may not be aware which visit this would be for the patient. So as soon as they enter patient number, it will give them a message that Patient already exists. So when they click Open Existing Patient Record, the form autopopulates with any notes from their previous visit into the respective page (i.e Page 1,Page 2 or Page 3).
Page 1 (Page 2 and Page 3 are exactly the same)
Patient Name
Patient Number
New Mole
Clinically suspicious
Changed from mapping Photo
Level of Suspicion: Score
Monitor
[code].....
If Multiple Conditions Exist Then Combine Text Fields?
Apr 1, 2014I am trying to build a list of account numbers based on conditions in a spreadsheet. Every account number will look like: XXXXXX-XXX-XXX, depending on if there is a 'Y' in both fields...see attached. I am desperate to get this done and have hit a complete road block.
View 2 Replies View RelatedCombine And Sum Rows Of Data In Excel Dependent On 3 Conditions?
Jan 3, 2014I have a dataset that, due to the nature of how it is generated, separates certain orders into separate rows. Which orders these are is irrelevant, I just need a quick and easy way to make excel re-combine these rows in to one entry, and sum up the data in one particular column in the process of doing this.
The separate entries of a single order can be identified by the data in 3 columns matching - I have attached an example.
The constituent parts of the same order can be identified when the country, city, and code columns all have matching data as you read down. E.g., where
Country City Code
a 1 12345
a 1 12345
a 1 12345
Where this is the case, I want excel to automatically sum the count of shops for this order and then delete all but one of these rows, with the remaining row now containing this summed figure. Failing this, a new worksheet being generated/a new column giving me this data would be good.
Combine Lookup And If With Point From Range
Jan 19, 2013I have two sets of example borehole data.
First, a range of depths with varying confidence levels.
ID
From depth
to depth
Confidence
PZ1
0
5
High
[Code] ..........
Second, point data where I would like to return values from within the range in the first data set. I would like the confidence column of the second data set to feed from the first data set, if the point depth data is within the depth range for a borehole with the same ID. Battled with all sorts of IF, AND and LOOKUP forumlas with no luck.
ID
Depth
Confidence
PZ1
3.1
Formula?
[Code] .......
Combine Pivot Lookup And Concatenate?
Dec 15, 2013I have a question to joint 3 functions : pivot>lookup>concatenate, to make it easier please have a look below table'
work hour
Dept
Machine
24
Seed
Seed-01
24
Seed
Seed-02
24
Seed
Seed-03
[code]....
From above basic table I need to make it compact to below format:
Dept
Work Hour
Machine
Seed
18
Seed (04,05)
24
Seed (01,02,03)
[code]....
what formula would suit the format above.
Lookup & Combine Names Seperated With Commas
Oct 30, 2008Example:
Original:
Column A | Column B
Joe | Client A
Joe | Client B
Paul | Client X
Sue | Client A
Sue | Client X
Want:
Column C | Column D
Joe | Client A, Client B
Paul | Client X
Sue | Client A, Client X
I believe it would be very similar to the code that I was provided inCombine Multiple Related Rows Into One. I tried to adapt it to my current need, but was unsuccessful due to my very limited knowledge of vba.
Combine Or Nest Multiple Lookup Functions
Dec 16, 2006I've got an indexmatch that works great
=IF(ISERROR(INDEX(accountstaff,MATCH(B$20,INDEX(accountstaff,,1),0),MATCH($ A55,INDEX(accountstaff,1,),0))),0,INDEX(accountstaff,MATCH(B$20,INDEX(accou ntstaff,,1),0),MATCH($A55,INDEX(accountstaff,1,),0)))
But I need to incorporate IF statement based on varying levels of revenue and think VBA will be less cumbersome, but don't know how to combine else if and index match.
I am trying to accomplish this:
IF B$20 (which is a dollar amount is = X, then index, match....
IF B$20 is > Y but < Z, then index, match...
Lookup Using Two Conditions
Jun 7, 2006I have a spreadsheet with data extracted from two systems. I need to compare the amounts to ensure they're the same. I'd like the "BIS" sheet to do a lookup of some sorts (either formula or macro) in the "Actual" sheet on box number (column E) and Category (column J) and return the total actual in (column I) next to the corresponding box number and category in the "BIS" sheet. I've attached an example of the spreadsheet.
View 6 Replies View RelatedUsing Lookup Function To Combine Text From Multiple Rows
Oct 4, 2012At a high level, I have several thousand rows of data. For the sake of simplicity, assume there are two columns: 1) Name; and 2)A comment (optional, could be blank). See below for example..
Sheet 1
Row 1: Mike │ "Great to work with"
Row 2: Mike │ "Bad manager"
Row 3: Tom │ "Great guy"
Row 4: Mike │ (blank)
Row 5: John │ "Cool"
Row 6: Mike │ "Best boss"
On a separate sheet, I want to be able to somehow use a lookup function to combine all the comments for each name in a nicely formatted package (notice how I skipped the blank space so it wouldn't take up a line.
Sheet 2
="Comments for "&[ref to cell containing "Mike" in another sheet]
- Great to work with
- Bad manager
- Best boss
Lookup Latest Value Given Conditions?
May 30, 2014see attached for the sample data. I'm looking for: Sat Score (from column F), that is from the latest/max Report Date (column A), for a Project (column B), THAT IS NOT BLANK
In other words: For each project, we want to know the score from the latest report. But sometimes the latest report doesn't have a score, so we want the score from the latest week that actually has a score. If none of the weeks have a score, return blank.
Lookup Based On A Few Conditions
Mar 13, 2008I am wondering if I can do a formula that would tell me if the name that appears in column J or K appears more than once at the time/date slotted in columns A and B. Basically, I want to make sure that the name(s) in column J and K aren't assigned 2 different places at the same time slot (column B) on the same say (column A).
View 9 Replies View RelatedLookup With Multiple Conditions
Dec 3, 2005way to return a value from a data table by specifying 3 conditions to be met.
Eg. My data table is in cells W1:Z100
(Column names = Track, Distance, Class, Time)
I want to be able to return the time value, based on track, distance and class values.
Lookup Based On Two Conditions ...
Jul 13, 2009I know that there are bunches of threads concerning lookups with multiple criteria, but I just can't figure out how to translate one to my situation. I want to return a value based on an item name which is in column A, and an operation which is in row 1.
The array from which I need to look up the value contains part number in column B, operation in column G, and the actual value I need returned in column H.
So I need to return something like this: ...
Lookup Maximum With Conditions
Nov 21, 2006i have a list of price list, and it is updated once a while, which means there is a change in version number.
The table is a direct extraction and it has to be like that...
I have tried to vlookup but only return 1 line, same as match and index... or maybe im not using the forumla correct.
Attached is the excel example hope this help. as you can see there is alot of dupe reference but different version and prices
I cannot concatenate the reference and version number as my lookup reference doesnt know what one is the latest version...
but one thing is constant, that is the version number is always needs to be the highest.
Lookup Based On 2 Conditions
Dec 22, 2006In the sample that I have attached. I am trying to compare Control ID and Business Date in Sheet[Test] and Sheet[Perform], If they are the same, then populate in Sheet[Perform] Column Test Status with the corresponding row in Sheet[Test]. note that Control ID and Business Dates are not constants.
View 4 Replies View RelatedLookup With THREE Conditions - One Vertical / Two Horizontal
May 23, 2012I have a table where I need to return a value according to THREE conditions:
1. Vertical, which is a shop code for instance
2. And 2 horizontal:
- One: segment (heading level 1)
- Two: product (heading level 2)
E.g. for "Shop1", what's the value for segment "PI" and product "Credit", etc.
An example file attached : fz73m5zEmZS6Y14-C.xlsx‎
The key thing for me is to do this WITHOUT changing data structure, because that really would be a huge pain. I mean, if it's no possible to do without changes (e.g. adding an extra column, etc.), I'll have to live with that I guess. But ideally I would really like to avoid changing data structure.
Conditions LOOKUP Two Files (ISNA)
Dec 21, 2006Let me first explain what I tried to do, before getting to the mess I created. Maybe I should use another formula?
I wanted to compare the amounts in two files on the basis of the same product number. Thus search for a sheet and a column in another file for the same value of A6 (current file) and show the value of the same row in another column (of the other file). However, if there is no value I wanted it to display zero (0) or a text such as "False".
First I did a basic LOOKUP which should work?. However it still showed #N/A for cells without a reference value (A6 is THA) or target cells without a target value (€100.00). I tried to avoid this by combining the ISNA function. However, although the last value shown is correct, the ones before change and seem to add up, or refer to another cell?
Another issue I did not come to yet is the fact that I would like it to search for the correct sheet based on the first three letters of the reference (A6), preferably not case sensitive. A6 is Tha13, first three letters indicate sheet name Tha. Any suggestions on this? ...
Lookup Value Basd On Multiple Conditions
Sep 25, 2008I could use some help with a look up formula with mutliple conditions. In my worksheet I have a column for an order number, date, activity, machine, job status code, unit of measuer and cost. I need to find the quantity reported by a specific actiivty and unit of mesaure for a speciific order number.
Here is an example:
I need to find the quantity reported in the 7th column for order #1 based on Actiivty "2" and U/M "m".
Order No.DateActivityMachineStatusU/MQuantityCost19/1/200811Cm101 $ 3.23 19/1/200821Cm317 $ 10.17 19/2/200812Az5 $ 43.25 29/1/200811Pm23 $ 2.50 29/3/200821Pm57 $ 27.50 29/3/200812Pz2 $135.00
Vlookup- Multiple Conditions For Lookup Value
Jan 8, 2009ABB
MetricsCategoryJan 2008VisitsTravel50view_offerTravel10
Above is the data I want to lookup on. I need to be able to do a lookup for travel visits and a seperate lookup for travel view_offers.
Is there someway to do this with a vlookup or maybe match index?
Lookup Multiple Conditions To Return One Result
Sep 28, 2009I am preparing an "automated" order form. I have a list of products (ten) in a pull down list, a list of options (3) in a pull down list and a list of the pricing.
So, If PRODUCT_A with OPTION_A, then PRICE = $X.
If PRODUCT_A with OPTION_B, then PRICE = $Y.
If PRODUCT_B with OPTION_C, then PRICE = $Z.
etc.
I want the user to select the product, then the option, and have the correct price "pre-fill" the cell.
Lookup Unique Based On Multiple Conditions ...
Jan 2, 2009I am looking for a solution other than using an advanced data filter for unique records only.
I would like to take a large list (columns A:D), and automatically filter for unique records and other conditions and paste the results in different tables (Group A & Group B).
Group A only includes records with value ="A" in the checksheet column.
Group B includes records with value <>"A" in the checksheet column.
Does anyone have any ideas how to do this so that when I paste the large list in columns A:D, the other tables (Group A & Group B) are automatically populated?
SEE ATTACHED:
Lookup Value From List Based On Multiple Conditions
Dec 21, 2007I have a list of holiday data which has the following info. Name, Date from, date to, hours off per day. I need to look up this information and place the number of hours against each day for each person. The problem I am having is that I can't get the hours into the cells for which the date is between the date from and date to. I have attached spreadsheet and how I want the summary page to look. I just need the correct formula inserting.
View 6 Replies View RelatedVexxing Lookup: Update That Supplemental Payment Based Upon Certain Conditions
Jul 1, 2009I have a list of employees who's job status may change at the end of a given month. If the status changes, they have a supplemental payment that is tied to their job status and may change as well. But that depends upon certain conditions like their years of service and their union.
I'm trying to write a formula that will update that supplemental payment based upon certain conditions, but I can't quite seem to get it right. A challenge is the table I'm looking values up in has 8 columns and the value could be in any of the columns depending on years of service.
These are the conditions for the lookup:
1. If their union local is the same, they would keep the supplemental payment so long as it is not more than allowed for the title. If it is more, they get the supplemental that corresponds to the new title and the years of service.
2. If the new title is PAA and they have 10 or more years of service, the new supplmental is $600.00
3. If the new title is Clerical Associate II and they have a 6 or more years of service, they get a supplemental that corresponds to their years of service.
Combine Formula Which Reduce The No. Of Additoanl Formula's Columns
Apr 9, 2009I have some student marks in columns B to F. If some one get below 40 marks he fails and the subject in which he has been failed will come in Remarks column. If he has passed in all subjects remarks column will show Pass.
To bring failed subject initial in Remarks i have to create 5 additional column and then thru Concatenate i joined them.
Is There any formula to show fail subject in Remarks column without creating 5 additional columns?
Combine Text Entree In Formula With Formula?
May 21, 2012Is there away to Combine a Text Entree in a Formula with a Formula
=IF('VEGAS - Main'!Z9=1,"MIXED DBLs","")
I am trying to do something like this:
=IF('VEGAS - Main'!Z9=1,"TEAM EVENT"+'Mixed Doubles'!P9
Result will be MIXED DBLs 1st Place
Add 2 Conditions To Formula Or Modify Existing Formula?
Dec 22, 2012I'm trying to add either a new condition to a formula or modify existing formula, so blank cells can't be taken as a number like 0 Here is the formula =IF(N14>0,1,0)
***This is what I do with formulas with similar problems =IF(COUNT(Y14,A14)=2,Y14/A14*100,"")
unfortunately the above condition its already a condition by itself so I don't know what to do whether to modify condition or add a new condition which I don't know how to do.
Formula: Combine The First Letter Of The Name
Mar 12, 2009I have 2 cells, one has a first name the other has a last name for instance:
A1 = Joe
B1 = Smith
I am trying to combine the first letter of the name in A1 with the next 3 letters in B1 so that C1 = jsmi