Match Two Columns Across Sheets And Based On Criteria Of Another Column

Aug 10, 2009

This is driving me nuts. What I have is:

ColumnB - number stored as text (Work ID)
Remaining Columns: - mixed data, some columsn numbers, some text

Sheet 2

ColumnA - number stored as text (Work ID)
ColumnB:ColumnAW - mixed data, some columsn numbers, some text
CollumnAX - Dollar Values

What I want is in Column F (Sheet1) a formula to:

if workID in Sheet1!ColumnB is present in Sheet2!ColumnA AND Sheet2!ColumnAX>1 then return True

View 9 Replies


Match 1st And 2nd Columns In 2 Sheets And Return 3rd / 4th And 5th Column If Match

Dec 15, 2013


What im trying to do is match columns A & B from AUDIT Sheet to Columns A & B in MASTER sheet. If they match then pull columns C, D & E from MASTER into AUDIT.

View 9 Replies View Related

Match 2 Criteria And Search Column And Return Value Based On Value

Feb 14, 2014

I have a spreadsheet that contains data for a fiscal year broken out by month and quarter. I want the formula to return the data from that month and use whatever the latest quarter is. For example in the data below:

If it has only pulled data only thru Q1 it would return Q1 for Jan-Mar, however once I have Q2 data I would want the formula to return Q2 for the months of Jan-Mar.


View 3 Replies View Related

Sum Column Based On Two Criteria From Two Columns

Nov 7, 2012

I'm not sure if this is the correct way to display my sample data, but here it is (how to change it. I looked in the guidelines, but I can't download anything onto this computer, so I can't get the HTML maker)


Total Vaue
Invetory Type

Total Incomplete Inventory Value

[Code] .......

Here is what I would like in:

The sum of the total value of half finished and unfinished inventory for each group.

So, for group one, it would be 145. For group two if would be 38, and group three would be 316. (displayed in column I)

I think an array formula would do this, but I'm not really sure how this would work.

View 9 Replies View Related

Copy Data Table Seperate Sheets Based On Column Criteria

May 12, 2008

I need to copy all the work sheets into one single work sheet (mastersheet). The source work sheets are having same column structure. The condition which i need to take care of is that after column 3 if at all there is any data till column 10 then in the destination mastersheet these should be copied in different rows with first two columns repeated. I need to do this using VBA macro.

View 9 Replies View Related

Extract Values From One Column Based On Two Criteria From Two Other Columns?

Feb 14, 2013

In the attached, sheet 2 has a formula that pulls the Item (column B) from Sheet 1 based on Instocks (column F) being less than the value in N2. Would it be possible to add a second criteria to the formula in sheet 2? In short, can I pull the item from column B of sheet 1 into sheet 2, where in sheet 1, column F values are less than N2 AND where column G values are greater than O2?

I'd love to just vlookup the fill rate value and then filter it down, I know at some point I will be asked to weed it down a bit via a formula instead.

View 1 Replies View Related

Sum Cells In A Column Based On Multiple Criteria For Neighboring Columns

Jul 1, 2013

Example data:




Basically I'm trying to write a formula that will sum the total of the numbers from column B but only where the month equals June AND column 3 says "Yes".

So the answer would be 101, because rows 2 and 5 are the only rows of that table that meet all the criteria, and the sum of their column B cells would be 101.

I thought I was getting there with Sumproduct and Sumif but I'm now drawing a blank. There is a little extra complexity in that I'm actually pulling the month value from a set of cells elsewhere, but if someone can solve the puzzle above I hope I can do the rest on my own as it's just locations of data to pull from other worksheets really.

View 9 Replies View Related

Finding Minimum Value In Column Based On Multiple Text Criteria In Other Columns?

Jan 2, 2013

This formula allows me to find the lowest value in column U where column N contains the text "NO".


I want to add another condition so that the formula only returns the lowest value in column U where (i) column N contains the text "NO" and also (ii) column F contains the text "YES".

View 11 Replies View Related

Extract Data From Few Sheets To Master Sheet With 2 Match Criteria

Jan 8, 2013

i want the compile all information from few sheet (20) with same format on daily basis based on due and CF status cash flow. the format as below as the result will same as original format,

Receive Date
Frank No


View 1 Replies View Related

Macro To Match Columns In Two Sheets

Sep 24, 2009

I just need an macro which can compare two sheets and give an output as matched or not matched.

I Have data in Sheet1 and Sheet2. In Sheet1 i have 4 columns of data and in Sheet2 aslo the same set of data.

If any of the four Columns of sheet1 or sheet2 is not matching then should pop-up not matched in the last column.

I Hope someone has the best solution for my query .For more clarification iam attaching an sample sheet.

View 4 Replies View Related

Match And Compare Columns On 2 Sheets

Apr 10, 2007

I have a list of names on 2 separate sheets that I need to modify. If I have a name on sheet1 matches a name on sheet2 I would Like to add a "space" then "(SV1) at the end of the text string on sheet2. I have a list of 1500 and will only have to modify 75-100. Example shows on sheet2 how I would like the desired outcome to be.

View 3 Replies View Related

Accurately Match Up Two Columns In Two Sheets To Do VLookup?

Aug 30, 2013

The columns that I want to match up are not exact matches. Multiple values from sheet1 colA can match up to one value in sheet2 colA by using the first 5 characters of the cell value.

Here is the workbook to go with the sample data set I put below.


As of now, the formula is generating a 2 in the banana row. But there are no bananas documented in sheet2.

How can I get my output to Sheet1 ColB to be accurate without having to create new columns/data with truncated values for the lookup?

If I use this formula in Sheet1 ColC (then copy downwards /autofill):

[Code] .....

to grab the Sheet2 colB. It works fine except for in the cases where sheet1 colA values have no match in sheet2 colA (i.e. Sheet1 A4-banana1).

That vlookup formula will output SOMETHING because of the nature of MATCH using either -1 or 1 as a match_type option. I cannot use MATCH if I want to solve this issue...but I do not know what else to do.



I know of a roundabout way to do this but I would like to stay away from "creating new data". (Create/insert a new column in Sheet2. The first row would be =Left($A2,5) then downfilled. This way I can use exact value in the MATCH formula in Sheet1's VLOOKUP.) If VBA is the only way I can "automate" this lookup output to be correct, then that is the road I'll have to take

View 3 Replies View Related

Index And Match Multiple Columns On Two Different Sheets?

Mar 13, 2014

I've got a problem where I am trying to return a value if two criteria from one table match two from another.

I have included a example of my issue.


I have had a look around lots of forums but cant get my head around what is wrong with my formula

View 4 Replies View Related

Check To Match Columns Separate Sheets

Mar 20, 2007

I have a column on sheet 1 which contains 200 lines with a unique number in each row (account codes). I have another sheet (sheet 2) which contains the same column. sometimes an extra row for an extra account code is added to the col in sheet 1. I need to make sure that the col I have in sheet 2 always has exactly the same as the col in sheet one, where the same account code should never be repeated. I am currently trying to setup a sheet where I keep all the checks and controls.

View 7 Replies View Related

Nested Index / Match Array - Return Value In Column C When Matching Column A And B But With Few More Criteria

Jun 7, 2014

I am trying to use a nested INDEX and MATCH array formula to return the value in column C when matching column A and column B, but with a few more criteria.

The range containing all the data


Cat 1
January 1, 2014

[Code] ..........

I am looking for the array formula to return the name of the person in column C who is in Cat 1 after the date in column B.

For example;
C7 should return "John" because B7 requests "January 15, 2014", which is after the value in B1
C8 should return "John" because B8 requests "February 15, 2014", which is after the value in B1
C9 should return "Andrew" because B9 requests "August 15, 2014", which is after the value in B4

The best try I had for the formula in C7 was


This brings back "John" as desired in C7, but when copying down the table into C8 and C9 both C8 and C9 return Andrew.

I guess this is due to my ">=" condition in the Match formula and it is returning "Andrew" because "Andrew" is also after the date requested, but I cannot for the life of me work out how to get it to work.

View 2 Replies View Related

Match 2 Columns Across Two Sheets & Copy Rows Of Matches

Jan 20, 2009

I would like to match column data in a source spreadsheet to column data in a target sheet. If a match is found, I would like to copy the corresponding row range from the source sheet to a separate, third sheet. For values where no match in found in the a target sheet, I would color the unmatched cell in the target sheet red. If a match was found, the cells would be colored green. The data in the Source sheet is in column A, while the Data in the Target sheet is in Column T. The data will be pased in the third sheet in Column T preserving original formats

I have this code, gleaned from several postings on this forum that somewhat works. The problem is that I get false mismatches (i.e. some cells get colored red even when there is a match and the data got copied to the third sheet) even though there are no duplicates. I have made sure that the formats are identical in both Target and Source sheets to try to fix this. Also, I don't want to cut the entire row , but just copy and paste a row range onto a third sheet. The column and row ranges are variable. I am attaching a file.!!

Sub CutRows()
Dim i As Long, k As Long, n As Variant, r As Range
Application. ScreenUpdating = False
With Sheets("Source")
Set r = Range(.Cells(1, 9), .Cells(65536, 6).End(xlUp))
End With
k = 0
i = 6
While Not IsEmpty(Sheets("Target").Cells(i, 20))
n = Application.Match(Sheets("Target").Cells(i, 20).Value, r, 0)
If IsNumeric(n) Then
Sheets("Target").Cells(i, 20).Interior.ColorIndex = 35
k = k + 1
Sheets("Source").Rows(n).Cut Sheets("Sheet3").Rows(k)
Sheets("Target").Cells(i, 20).Interior.ColorIndex = 3
End If
i = i + 1
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

View 7 Replies View Related

SUMIF Multiple Conditions (two Columns Match Criteria Add The Third)

Mar 17, 2009

Im trying to have a formula look at two diferent columns and if they meet the criteria add the third column.

I tried using sumproduct but It wont give me anything but error messages or a zero.

Looks like this

Need it to say:
if column A = Stewart and Column B = Fiduciary then add up the amount in Column C

View 9 Replies View Related

Chronological Listing & Match Criteria From Multiple Columns

Jun 14, 2007

I cant get this one off the ground. Normally I am pretty good to find bits and pieces of advice from other posts and put something together, but this one has me stuck from the get-go. I want to create several lists based upon the row(s) containing at least one set of criteria (day and time, contained in two differnt columns). Each row allows for up to 3 sets of days and times (i.e. columns B and C, columns D and E, and columns F and G) Each row is date and time stamped as to when the data was entered in that row (column H).

I want one list for each set of the various day/time combinations allowed in columns B thru G. The list should provide the name(s) contained in column A, in chronological order based on column H, when a criteria match is made in columns B:C, D:E, or F:G. This is far easier to understand with the attached sample. The lower section of the sample represents my desired output that I cannot seem to achieve.

View 3 Replies View Related

Find Closest Match Based On Two Criteria

May 12, 2009

I have a sheet of pump test results, placed in rows, where the test rig is set at a specific speeds and flow and it records pressure. At each stage of the test a number of snapshots will be taken (Usually between 3-5 at each stage), so I have 3-5 rows of very similar data. I only need to use the best row from the selection available, so I am hoping to find a formula or code (Don't care which) that can look at the results and select the row of readings where the speed AND the flow are closest to the targetted data.

For example: In cells G6:G8 I have flow result values 0.129, 0.151, 0.156 and in H6:H8 I have speed result values 72.536, 71.82, 72.13. These are actuals. In cell N6 I have the target speed (75) and in O6 I have flow target (0.15), but these could be moved anywhere to suit really. What I was hoping to do, but am struggling big time, was to just flag up the row of readings where these two values are closest to the targets by placing a check mark in column L:L which I have formatted in monotype sorts and would like to place a "4" (Which gives me a check mark) on the 'best' row. From that I can do more with some simple IF statements.

Is this feasible, or am I trying to do something beyond Excels capability, it is certainly beyond mine at the moment. I was looking at the check mark method, but that is not essential, so if anyone has any suggestions on an alternative means of identifying the best row of data, that might make it easier to do, then I am listening.

View 9 Replies View Related

Using Index And Match For Two Criteria - One In A Column Other In A Row

Dec 20, 2012

How to get the following index/match formula to work.

I have 2 criteria that I need to find the result of in a table that has the one criteria down the column, the other across a row at the top.

The formula I have is thus far:


In this L98 is the criteria I'm in theory doing a vlookup against the values in column $K$27:$K$82, and N98 is the theoretical hlookup against row $K$15:$AV$15, bringing back the intersection point from row $K$46:$AV$46

All I get is #N/A .....

View 5 Replies View Related

Macro To Match Column Against Row Criteria

Feb 10, 2014

Trying to write a macro that will display either an 'X' or a date under a specific header if several conditions are met across a row in another tab.

I have two tabs, one is the report, the other raw data. On the raw data tab, if the macro finds a row where the title, territory, Rowdesc, start and end dates all match criteria, then display an 'X' next to that title in the report tab. The criteria would be:

Title Match - between Raw Data tab and Report tab
Terr_Nm column matches report header in Report Tab
Rowdesc = 'No Rights'
Start Date = the date 1-Jan-1900 or 1
End Date = * - an asterick

However, if the macro finds a start date that is greater than today, display this start date on the report where the 'X' would normally go. If the macro finds a start date that is less than today and an end date that is greater than today, then display the end date where the 'X' would normally go.

If the macro does not find anything, then do nothing and go on to the next row. In my example, 'Title 4' isn't on the raw data, so the macro would do nothing with this title.

Raw Data:

Terr X
No Rights

[Code] ....

Report Tab, before running macro:

Terr X
Terr Y
Terr A
Terr B





Report Tab, after running macro:

Terr X
Terr Y
Terr A
Terr B


[Code] ......

I've tried doing this with formulas (dcount, countif, etc.) and the problem is displaying the specific date. So, I'd like to use VBA to automate.

View 1 Replies View Related

Match Same Column In Different Sheets And Paste Row To New Sheet

May 30, 2006

I'm looking for a formula that will match columnA sheet1 with columnA sheet 2 and where theres a match (alphanumeric) will copy the whole of the row in sheet 2 onto an entire new sheet.

View 3 Replies View Related

Index Match Based On Date Range And Name Criteria?

Jan 1, 2014

I would like to ask how can I get to work the index match function (if there's any formula other than this current function its fine) if i have a start date and end date as range date then another criteria for name to get my desired result. im having a hard time to explain so i attached a workbook so you can around it.

I found a formula and tweaked it but still cant get to work.

View 10 Replies View Related

Insert Rows Based On Criteria Match From Sheet2

Mar 12, 2007

There are many columns in Sheet1 and more will be added for titles of the columns remains unchanged. One of the Col. in Sheet1 is 'Part Number'. Part Numbers are defined as 'Configurations', and each Configurations has additional parts and peices which are broken down in Sheet2. Back in Sheet1, I also have a Col. for QTY. So, if Sheeet1 has a part number that matches in Sheet2, then I would like Excel to copy all 'parts and pieces' and QTY from Sheet2 to Sheet1. Please make sure that I want the QTY to be multiplied if applies. Please see below for an example:

Sheet1 looks like this:...................

View 2 Replies View Related

Show Info In Column C, If A & B Match Criteria

Aug 20, 2009

What I'm having a hard time is trying to get information from column C to pull into a cell, for a specific student (column A), but only when the entry happens between two set of dates (column B).

Column A = Student Name
Column B = Entry Date
Column C = Entry

So in a different worksheet, the names of the students will already be on the report and each column following will have a date range of a week. So for column B I want the entry that only happened between "09/07/09-"09/13/09". No student will have more than one entry a week so I just want it to locate the entry and fill in automatically. This will repeat for each week after as well.

View 3 Replies View Related

Copy Rows Between 2 Sheets Based On Cross Reference Match

Apr 22, 2008

I am trying to drop (paste) in new data in a range in sheet 2 and cross reference a column with a table in sheet three and display all rows of matching instances in sheet 1. Example:

Sheet 3 has
a1 b1
amcdap amber connor
apsdap ashley simpson

sheet 2 has
a1 b1 c1 d1 e1
amcdap 300 400 2:00 9:00
apsdap 500 300 4:00 8:00
capdap 200 300 5:00 9:00
dlsdap 400 300 2:00 8:00

I need to return only rows 1 and 2 to sheet 1. I guess ideally I'd like to drop data in sheet 2, click button.

View 5 Replies View Related

Match Columns Based On An ID

Feb 8, 2009

I have a spread sheet with columns like this:

1 a a
2 b a
3 c b
4 d b
5 e b
6 f c
7 g d

Column A is an ID for manufacturers. Column B is the manufacturer name. Column C is the order in which the manufacturers appear. There are duplicates in column C only. They are supposed to be there, and they must remain there. I need column C to match up with the IDs in Column A so it can look like this:

1 a a 1
2 b a 1
3 c b 2
4 d b 2
5 e b 2
6 f c 3
7 g d 4

View 2 Replies View Related

Multiple Sheets All Reference The Same Column(s) To Match Data?

Mar 7, 2014

I've created 12 identical work sheets for the year, each has 6 columns of data and 87 rows all containing different information (letters, numbers, dates and characters).

I've also set up a "Conditional Format" rule so that any identical information triggers a strikeout (line through the cell entries) of the entire row for both lines of offending duplicating data. To do so, I created a use cell off to the side (in column AT) with the command =CONCATENATE (u41,v41,w41...) which condenses all data into 1 cell.

Afterwards, I went into Conditional Formatting and created the strikeout command with =COUNTIF($A$T$29:$A$T$116,$AT29)>1; where the $AT29 reference simply identifies the cell to the left and then continues down.

This has worked well but I'm stumped. Now I want the reference to extend to the previous month, lets say I'm in February and want to extend the search to Januarys AT29:AT116 review.

simply use a reference in the COUNTIF formula in the Conditional Formatting rule that takes the search through to the January work sheet, then I can simply duplicate the command for the March sheet which will then review all 3.

View 1 Replies View Related

Printing Sheets Based On Criteria

Jul 10, 2009

I am looking for a macro that will, upon command, print all sheets (20+) in the workbook with a value greater than $0.00 in cell I27. Some have a value there, others don't.

View 2 Replies View Related

Two Sheets - Lookup Based On Several Criteria

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

Copyrights 2005-15, All rights reserved