How To Match Multiple Column And Return A Value

Aug 22, 2013

I want to write a macro that match multiple column and return a value as I show in the table below

Posting Date


[Code] .....

I have to check multiple column from 2sources and get the Posting Date from Table2 to Table1. I'm quite confused about the function which I should use like match, lookup, index, etc.

View 4 Replies


Index And / Or Match Formula With Multiple Match Required To Return A Value

Jan 21, 2014

See attached file, "Rate Sample Index-Match Formula".

I need a formula to return the value at the cross section of two (2) lookup values that match. This formula will be input into column D under, "RATE" on the 1st tab, "TEST FILE".

In the 1st tab, "TEST FILE" there are a series of columns as follows;

A = Service
B = From
C = To
D = Rate

In the 2nd tab, "RATES" there is a series of rates with drivers From (green) & To (blue)

The formula needs to do the following;

1. Lookup the "From" value in column B on tab, "TEST FILE" and match to column B2:B59 on tab, "RATES" both highlighted in green
2. Then Lookup the "To" value in column C on tab, "TEST FILE" and match to row C1:BH1 on tab, "RATES" both highlighted in blue
3. Then return the value at the cross section of the match "From" (point 1 above) & "To" (point 2 above) in range C2:BH59

For Example;

The rate From SYD To CBR = 0.33. I have highlighted this in yellow on both tabs to show where the formula needs to lookup the data to return the answer.

Additionally, if we were to add service as an additional lookup match how would this work?

View 3 Replies View Related

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

Lookup/Match: Compare A1 For The Values In Column B, Then Return The Corresponding Cell (column C) In Column D

Jan 31, 2008

I've been searching the forums for this problem but I can't seem to find any answers. Anyway, this is the problem. See screenshot.

I want to compare A1 for the values in column B, then return the corresponding cell (column C) in column D.
e.g. D1 = 2, D2 = 1, D3 = 4, D4 = 5 and D5 = 3.

View 9 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

Lookup Up/Match: Return A Value In Column A If My Data Matches Column B?

Jun 5, 2009

I have a tab that has 2 columns of data and I want to be able to return a value in column A if my data matches column B. If column B has the text TRUETRUE, I want to bring back the corresponding data in Column A. How do I return all the data in Column A for all the TRUETRUEs in column B? I can only get the first instance of TRUETRUE.

View 4 Replies View Related

Want To Return Value From 2nd Column Based On Match In 1 Field With First Column

Sep 23, 2009

I'm working on a project in which I have 50 states + DC listed by 2-letter abbreviation, and want to return a value in another column that will be a non-unique number (some will duplicate in the second column.)

Also need to do this same operation with first column (state) and third column, which also is comprised of non-unique numbers.

Hlookup, lookup & pivot tables seem to not be a fit for this.

View 9 Replies View Related

Match Text In Column Of Another Workbook & Return Nth Column Same Row

Mar 12, 2008

I have two excel files.
File #1 is a varying length and has 4 columns with ID #s in Column A.
File #2 is a two column file that contains a list of ID #s in column A AND modified ID#s in column B.

I need to match the ID #s in column A from file# 1 to the list in column A of file#2 then replace ALL instances of the matching ID #s in column A of file#1, with the values found in column B of file #2.

I've tried using the substitute function but I could only replace one found ID at a time.

View 9 Replies View Related

Return Column Name On Match

May 30, 2012

I am trying to return the column name to the user based on a cell value. I am using the formula =Match(C2,1:1,0). This returns a numeric value of the column based on the match. What I want to get back is "AL". Do I need to use some format calculation to achieve this?

View 5 Replies View Related

Match One Value And Return Multiple Rows From Another Sheet?

May 13, 2014

I have a large data set which is basically just transactions on accounts by date and I want to return the transactions in date order by account number. I've attached an example where sheet 2 is the transaction listing and sheet 1 is what I'd like to return.

View 3 Replies View Related

Can Index Or Match Return Multiple Results

Nov 27, 2006

I've spent the last week trying to do the following, with little to no success....and I'm running out of hair to pull out!!

I have a worksheet (let's call it DATA) with the following column headers:
* Site Name
* Year Initiated
* Status

plus a whole bunch of other columns of information.

On a separate worksheet (let's call it RESULTS) I'm trying to create a summary of all the Site Names where the Year Initiated = "2004" and the Status = "Active". There will be multiple occurences where this is true.

I then want to create further results where the year = 2005, 2006, etc., etc. I've been trying to use the INDEX function with variations of MATCH, SMALL, ROW, etc., etc., but so far, no luck.

Advanced Filtering is not an option as I need to do further manipulating of the results on the RESULTS worksheet.

View 12 Replies View Related

Match Two Criteria And Return Multiple Rows

Oct 26, 2011

I'd like to return multiple rows with two matches on criteria (ID and First Term). I'm able to return all grades for a given ID but do not understand how to get the second criteria, First Term, in my statement.

=INDEX($F$2:$F$12, SMALL(IF($A16=$G$2:$G$12, ROW($G$2:$G$12)-MIN(ROW($G$2:$G$12))+1, ""), COLUMN(A1)))

Code reflects the ALL GRADES data below.

1 Biology 301 12345 4 B 123456
2 Biology 302 23456 4 B 123456
3 Biology 303 34567 4 A 123456
3 Biology 338 65432 4 C+ 234567
3 Biology 338 54321 4 A 345678
1 Biology 336 43210 5 B+ 456789
1 Biology 337 43210 2 A 456789
1 Biology 338 65432 4 B+ 456789
1 Biology 301 12345 4 A 567890
2 Biology 302 23456 4 A 567890
3 Biology 303 34567 4 A 567890

Row Labels Min of TERM Count of GRADE First grade All grades
123456 1 3 B B B A #NUM! 234567 3 1 C+ C+ #NUM!

345678 3 1 A A #NUM!

456789 1 3 B+ B+ A B+ #NUM! 567890 1 3 A A A A #NUM! Grand Total 1 11

* Need all grades where TERM = Min of Term

View 7 Replies View Related

Match Date In Column & Return Corresponding Value

Jun 17, 2009

I have used index/match before to match and extract data, but I can't seem to wrap my head around this one. It's probably best explained by looking at the attached workbook. Basically, I want formulas in 'John Doe!B' and 'Jane Doe!B' to look up the Name and Date values in column A on the 'Data' sheet, and then return the time value that is on the same row but in the 'Data!B' column.

I then want 'John Doe!C' and 'Jane Doe!C' to return the time that is two rows below the time shown in 'John Doe!B' - I've gone and manually entered and bolded the values in the workbook to show what I eventually want the formula to do. The data on the 'Data' tab will be extracted monthly into excel from tracking software my company uses, but there will always be a minimum of 3 rows of time for each date. As you can see in the example workbook, some dates will be omitted for some people, and while there will always be 3 minimum rows there will often be many more time entry rows for different dates as well. However, I'm only concerned with the 1st row and 3rd row for each date.

I was originally thinking a vlookup for 'John Doe!B' and then a hlookup for 'John Doe!C' that references the value in B might work, but I'm getting stuck on how to ensure the formula realizes the differences between the data and dates for John Doe and Jane Doe.

View 3 Replies View Related

Match Data In Column Against Another & Return Value From Another

Sep 27, 2007

I have data in 1 column that I want to match data in the second column. If it matches a value in the second column I would like it to return the value from a row in the third column.

View 9 Replies View Related

VBA Match Function With Multiple Criteria To Return Row Number

Jul 16, 2012

I'm trying to figure out how to write a VBA Match function that can look for multiple criteria and return the row number of a successful match.

I have about 255,000 rows of data on the worksheet "Filtered". Column B contains my Item Number and Column D contains the supply source. I want to find the row where ItemNumber and SupplySource match my variables and then return the value from Column C.

I can do a match for one criteria, but where I'm having problems is getting it so the two matches are on the same row.

View 4 Replies View Related

Compare Two Columns And Return Match From Third Column?

Mar 5, 2014

I must match column A and column B to return the column C In colA there are all my countries In column B there are all my countries code and countries I want get the colC where all my colB match than colA.

View 14 Replies View Related

If Two Columns Match - Row And Column In Another Sheet Return A Value

Jan 11, 2013

I have a file with multiple column headers and data entries (dropdowns) into the rows. I wanted to create a secondary a pivot table..if the vertical and the horizontal are both in the two columns selected it returns a value in the table.

I would use a pivot table, but it is a data entry sheet and the pivot table does not bring up all the horizontal and vertical values unless they have already been selected in the sheet.

View 9 Replies View Related

Find Match In Column & Offset For Return Value

Sep 2, 2007

i tried using the lookup but it gave a different result. i want to search a value from sheet1 A to sheet2 A and copy the remarks from sheet2 B and paste it to sheet 1 B and if not found leave it blank.

here's the attach file i just trim it down.

View 5 Replies View Related

Return Multiple Values That Match Single Search Criteria?

Nov 28, 2012

Attached is a sample workbook, but essentially what I'm looking to do is automate the process of searching through a data set where the value of interest (in this case, names) often has multiple entries, with different values attached to each instance.

I would like to be able to get a list of all values in a given column that match a specific name in another column.

Currently I'm using a basic INDEX/MATCH search just to see whether the data exists at all, but that's only half of what I have to do here, and I'm totally stumped on how to get a comprehensive list of all matches.

For reference, if you look at the sample, what I need is a list of all values in the "CPT" column that match the name searched for in the first column.

The actual data set size is at most 3-400 entries, if that makes a difference in how to approach this.

CPT Sample Book.xlsx

View 4 Replies View Related

Match Cells In 2 Columns And Return Third Column Value If Equal

Dec 24, 2013

I have 2 sheets with thousands of clients. One has client contact information and one revenue. I was able to put the 4 columns on 1 sheet. I need a formula that will say if the value of the client name columns match, (Client name) then return the value in the third column (revenue).

One other question, b/c i copied from a pivot table, i have the client name and their email address below in a separate row. Ideally i would have the client their contact and their revenue in 3 columns next to each other. Is there an easy way to bulk move the email address from the row below the column to the column next to the client name?

View 7 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

Match Cell Content With Another File And Return Value From Next Column Over?

Sep 29, 2013

I have two files. They both have US census tract numbers in a column GEOID2010. One also has a CSA2010 text description associated with each census tract.

I need to match the cell with a census tract in the first file (example1).xlsx with the census tracts in the second file (example2.xlsx), and then return the CSA2010 text from the next column over to the first file.

I've attached two example files.

View 4 Replies View Related

Offset / Match - Formula To Return Left Column Value On Same Row?

Feb 16, 2014

Latest Status
Latest Date
Current Status 1
Current Date 1
Current Status 2
Current Date 2

Sun,16-Feb-14,11:0 PM
Sun,16-Feb- 14,10:41PM
Sun,16-Feb- 14,11:02 PM

My formula =IF(B3="","",OFFSET(A3,MATCH(B3,B3:F3,0),0,2)) in A2 is giving me #value error.

The correct solution should be Take2, while matching the condition from B2 latest date.

Sheet Attached. File Name-Latest.xls - Latest.xls

View 5 Replies View Related

Index Match Lookup, Variable Return Column

Sep 3, 2009

I would imagine that the title of this thread made little sense, so let me try to explain:

Sheet A has columns A-C populated with data. Column D needs to search Sheet B columns A-C for a match. (ie, see if the data on sheet A is also on sheet B) if this is true, then it needs to return the value of the LAST column of the matched row in Sheet B.

So, if Sheet A R1 A-C is 1,2,3. Sheet A R1 D needs to search Sheet B for 1,2,3. and when it finds it, (for example on row 9) return the value of the last column of Sheet B row 9. The issue is, the last value could be in column R or S or AA, there is just no way of knowing.

View 9 Replies View Related

Lookup Column Values In Another & Return Value If Match Found

May 28, 2008

I need to create a macro that will loop through a list of names on Sheet 1 / Column A, and if that name is listed on Sheet 2 / Column A, then it will set Sheet 1 Column B (for that one name) to "1".

The problem I've run in to is that both the lists change in length each time I have to run this (so no set range).

I've been able to get this to work with two ( nested) loops, where the first loop goes through the Sheet 1 list and the nested Loop looks for the name on Sheet2, but it is extremely slow.

Is there any simple VBA code or formula that I'm missing that could do this? I've tried to do something with the " Find" VBA command, but I can't see how it would apply to this if the list changes in size.

View 8 Replies View Related

Lookup Match And Return Comment Based On What Is In Relevant Column

Jan 15, 2014

The logic of the formula like this....

TAB X, Column A contains a data set, each row is a unique value. In column B of TAB X, I want to input a formula that performs the following logic: Search TAB Z, Column C, for an exact match of data contained in TAB X, Column A....and if found, look in TAB Z, Column D...if a blank cell is contained there, then in TAB X, Column A, say "Unsettled" ; however, if there does exist any character (not-blank) in TAB Z Column D, then in TAB X, COlumn A, say "Settled".....but, if the data ID listed in TAB X, column A is not found whatsoever in TAB Z, Column C, then say "NOT FOUND". So for example, within TAB X, Column A there is a unique ID in one of the rows "ABC123", then searches "ABC123" in TAB Z, Column C. If found, it looks in TAB Z, Column D and that cell is blank, so TAB X, Column B, same row that contains ABC123, the result is "Unsettled".

View 1 Replies View Related

Match Function To Return Column Number For Matching Date Errors?

Aug 5, 2014

I am trying to use some vba match function code to return the column number of the matching date. The date will be stored in a date variable.

Every time I run this code I normally get a match error even though the date is in the worksheet and the variable matches that date.

See below:

[Code] .....

View 5 Replies View Related

Lookup/Match Column Values On Another Worksheet & Return Adjacent Cells

Jul 1, 2008

I have two excel sheets. The first sheet has a column with codes and another column with dates. There are more than one date for the same code. The second sheet has just one unique code. I would like to match the code on the second sheet with the code on the first sheet and return ALL the dates associated with that code to the second sheet - horizontally.

View 5 Replies View Related

Match Multiple Values With Multiple Values From Another Table - Return Single Value

May 12, 2014

I am trying to look & match key values from 2 areas of one table with two areas of another table; in turn, it'd return one value based on the lookup table...

Attached worksheet : Test booklet.xlsx‎

View 4 Replies View Related

Formula To Find Matching Values In One Column And Return Totals For All Rows That Match

Dec 26, 2013


I am looking for a formula that will satisfy the following:

1) find all the values in column "A" that match
2) In column "G", sum up all the values in "F" that go with the matching values in column "A"
3) For example, in rows 14-16, the values in column "A" match. Cell G16 sums up F14:F16

View 3 Replies View Related

Copyrights 2005-15, All rights reserved