Match Function With Additional Data?

Aug 13, 2013

Trying to perform a match of 2 sets of data.

Data set 1 is 2 columns: A = ID number, B = Text entry
Data set 2 is 1 column: C = Text entry

All of the cells in column C can be found in column B so I'm trying to match that data but I also want to create a new column that lists the ID number for the matched cell.

For Example:

Column A
Column B
Column C
Desired Result

1
Sock
Underwear
9

[code].....

I've tried to do this MATCH and VLOOKUP. Kept getting error messages. I threw a stapler.

View 5 Replies


ADVERTISEMENT

VLOOKUP / INDEX / MATCH Function: Match Data From 2 Independent Sets??

Oct 8, 2009

I am trying to match data from 2 independent sets, formatted slightly differently so not sure which function would work best for me. From the attached file, I am trying to match the date and time stamp (in cell A1) with that from the other data (in this example in cell E1) and return the data (from cell F1) to cell C1. So basically any date and time stamp before 04/03/09 04:00 will return a value of 44 (this value should appear, therefore in cells C1 - C30)

View 2 Replies View Related

Vlookup - Finding Additional Values From Duplicates Using Additional Column

Jan 29, 2014

I am using a spreadsheet as a score sheet for a competition. One of the columns is the student's GPA. After entering all the scores there are duplicate final scores. I need a way to have it look at the final score and then use the GPA so that it will not put a duplicate value in the final column.

al Column N is the Total Column, Column O has the Names that correspond to the Total Column. Currently I am taking this total and putting it into Column Q (High Scores) in high to low order. Column R should have the names that match the scores. But with duplicate scores, it is only putting the first name associated with the score. I would like to use the GPA as a final determining factor for the duplicate scores. The higher GPA would come before a lower GPA. I have tried to put an additional column to bring the GPA over to correspond with the High Scores Column, but could not get it to work.There are actually more names for the competition and the top 10 will be moved to a different sheet and further judged. I have attached a sample with the exact formulas that I am using.

View 1 Replies View Related

Data Match Function

Nov 20, 2008

I have one column full of duplicate names, column A. I have another 2 columns's E & F which contain the unique number and corresponding name to that number. I want to create a formula which i can put in column B which will match every name in A to the name in F but will enter the value as the unique number.

ie

A1 will contain Joe. E1 = 1 F1 =Joe. When I enter the formula in B1 it will show as 1. How would I write this as a formula?

View 5 Replies View Related

Index Match Vs D Function To Summarize Data

Oct 24, 2008

I have a project that has several worksheets of "summarized" data all pulled from one worksheet. The data worksheet is run through an external program and pasted into the workbook to update the information.

To this point I have used tons of index match array formula's to pull the information but I've found this to be extremely inefficient because there are hundreds if not thousands of formulas, and it takes several minutes to update. The file is also around 3MB which is way too big.

I was thinking about using VBA to simplify the worksheets, but then I came across an article about D_Functions and I'm thinking they may be the solution to my problems.

However there is one catch...I'm not sure how I would build these formulas to work in my worksheets. I understand the principle behind the D_Functions, but I can't seem to grasp the concept of integration and making it work in my scenario.

I've attached an example of this workbook, it has most of the forumla's deleted out to reduce the file size, but it should give you an idea of what I'm doing. Once you open it you'll have to unhide some columns to get to the nitty gritty of the sheet.

Feel free to take a gander...I'm open to any suggestions you may have at this point (except to trash the whole project hehehehe ). If you find a solution please explain the concept behind it. I really want to learn how to make this work rather than plugging in someone elses formulas.

View 10 Replies View Related

Getting Additional Data From A Reference

Jun 21, 2009

I am building a database of music files and using Excel(2007) to catalog them. As an extra bonus I'm setting up a sheet of statistics about the database with suchs things like the # of tracks and longest song, etc.

Most of it has been easy to figure out but there's one formula I'm not quite sure how to do, or the way I think it is to be done is incorrect or contains some error on my part. I already have a cell that gives me the length of the longest song in the database. What I want it to do is give me the name of the longest song. This is what I tried to do, without success, giving me an popup error telling me that the formula has an error.

View 3 Replies View Related

Copy Data Of Index Match Function According Date?

Jun 24, 2014

I have the first sheet named "DPF" where I have a column "Date", "FIT", "Group" On my second Sheet named "Tracker" what I do want to do:

I select a date for example 18-07-2014 (Cell E3), if cell E4 I Have "Fit" and F4 "Group" In C6 I have E3-15, to get the date minus 15 days (03-07-2014) In C7 I have E3-12 (06-07-2014) and so on

What I want is, if the date of today is C6 or C7 or whatever, use the index match function to get the number corresponding to the date chosen (18-07-2014) for the FIT. But, once I get the figure, I want to keep it, because with the if function, once we are the 4th of July, the figure for C6 disappears...

Fri
18-Jul
FITGroup
Start
D-1503-Jul
D-1206-Jul
D-909-Jul
D-612-Jul
D-615-Jul
D-216-Jul
D-117-Jul
D18-Jul

So either a macro to copy the specific data into another sheet or a another way to keep it. Because the idea as the end is to do different scenario based on the result got per period..

View 2 Replies View Related

How To Handle Duplicate Data Using Index And Match Function

Feb 4, 2014

How to deal with duplicate data in excel using index and match function. Is it possible to use the following function without having a unique value?

View 1 Replies View Related

Link 2 Worksheet For Additional Data

Jun 18, 2008

The code is as follows:

Sub addtaskpc()

ActiveSheet.unprotect
Application.DisplayAlerts = False
myrow = Cells. Find(" Total P&C Estimate").Row - 3
mycell = Cells(myrow, 2)
mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1

With Range(Cells(myrow, 2), Cells(myrow + 2, 2))
.EntireRow.Copy
.EntireRow.Insert Shift:=xlDown
End With

Application.CutCopyMode = False
Cells(myrow + 3, 2) = "Task#" & mynum
Application.DisplayAlerts = True
ActiveSheet.protect

End Sub

View 9 Replies View Related

Dropdown List And Bring In Additional Data

Feb 18, 2014

I want to use excel to create quotations for customers. (currently doing it in word)

Tab 1 is the quote, has 4 columns, part number, description, cost, yearly maintenance
Tab 2 has all the things we sell, part number, description, cost, yearly maintenance. 150 lines.

So, Tab 1, description column is a drop down box from Tab 2. Working fine.

Question. When an item is selected from the drop down, how do I get it to then bring from tab 2 the part number, cost and yearly maintenance.

View 8 Replies View Related

Finding Matches And Adding Additional Data

Mar 17, 2014

Sheet 2 in the attached sample workbook (without all the pre-existing formula's from the original) contains data that I paste in (Cols A to D).

Sheet 1 then searches that data (minus Col D) from Sheet 2 and returns "Yes" to Col I if matching data is found.

What I would like to do now is if Sheet 1 returns "Yes" then the corresponding value from Sheet 2 would be returned to Sheet 1 Col J.

So in this example the value "5" would be returned to Sheet 1 Cell J1

I already have all the formulas in place to find the matches and return "Yes", this is just to return the additional information from Col D Sheet 2 to Sheet 1 Col J..

View 3 Replies View Related

Data Validation List + One Additional Value + Default Value

Feb 20, 2010

I would like to set a data validation list with

- the list of values (A1:A10)

- at the top of the list the value "all"

- as default (when opening workbook or when changing depenend validation list) = "all"

View 9 Replies View Related

Exiting A Range To Grab Additional Data

May 8, 2006

I've set a range as DD14 to G014, then using a For Next Loop asked the program to cycle through each cell in the range. Upon findng a populated cell, it stores several bits of data from that cell (already done) and then move up one cell to record the cell value (time) and again move one cell up to record the date.

Now I did get this to work using a clunky bit of code that used activecell.offset to move the focus up the two columns and a While .... Wend loop - but using a set range and cycling through each cell it does not appear to be possible - or am I wrong?

Set MyRange(1) = Range("D14:ER14")
Set MyRange(2) = Range("D23:GN23")
For MyCounter = 1 To 2
For Each Cell In MyRange(MyCounter)
If Cell.Value = "" Then Goto Skip_Cell
MyCell = Cell.Address
MyPosition = InStr(1, Cell.Value, ",")
If MyPosition = 0 And Len(Cell.Value) >= 3 Then
Tool_Number = Left(Cell.Value, 3).....................

View 2 Replies View Related

Index - Match Function To Output Data Based On User Input

Apr 30, 2009

I have a list of data that I'm using the index - match function to output data based on user input.

For example, I have:

1 10
2 15
3 20

So when '1' is selected another cell outputs '10'.

However what I want is for example if 1.25 is input, I want it to interpolate to spit out the correct number between 10 and 15.

View 5 Replies View Related

Lookup Data From Multiple Worksheet Using Indirect Address And Match Function

Sep 28, 2011

determining the correct formula to lookup various details from multiple worksheet:

Data as follows:
Sheet1: input
AB1MonthSales2AUG55003MAY4000

Sheet2: Data

AB1MonthSales2JAN20003FEB25004MAR30005APR35006MAY40007JUN45008JUL50009AUG550010SEP600011OCT650012NOV700013DEC7500

b2,b3 Formula = Indirect(Address(match(a2,????,0),1,1,1,"Data"))

i dont know what is the correct range inside the match fuction to identiy the row number inside the Data sheet, i think the solution would be match(a2,Data!a2:a13,0) but things might get complicated if you have multiple worksheet such as sheet2: Data , sheet3: Data2, sheet3: Data 3

and each table has unsorted details compared to others sheets (Data vs Data 2)

View 9 Replies View Related

VLookup To Pivot Table Using Match Function - Returns Error If Can't Find Match Value

Mar 11, 2014

I am having some trouble getting a formula to work. I am building a report that pulls figures from a pivot table in another workbook. I am using a vlookup with match function to get the column index to find the relevant data I want. Where I need to add two columns together I am using sum, with the vlookup & match formulas nested in them e.g.:

=SUM(VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("FAID",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("COMM",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("BPCM",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("COMD",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE))

Where:
F13 = Employee number
Column C on the pivot 156 workbook is where the employee number is based.
The Match formula is then getting the column index from the column headings of the pivot table ie. "FAID"

This in itself works fine, as long as it finds a match in the column headings. This is where i get the error as in the above function "COMD" is not in the pivot table. However I need to keep it included as it may appear on a future pivot table. Is there a way of getting the sum function to complete even though later in the formula it can't complete the vlookup? So it will ignore it, or assume the value is zero if it can't find it? The formula probably needs to do this for all the vlookups as some headings may drop off in future pivot tables.

View 2 Replies View Related

Check If Values Exist In Range And Move Them To Another Workbook With Additional Data

Jun 9, 2014

I have 400 source files containing (among others) 8 sheets with daily results: "Fri 23", "Mon 26", "Tue 27", "Wed 28", "Thu 29", "Fri 30", "Sat 31 (if applicable)", "Mon 2".

Each sheet contains also:
State - D1
Role - D2
Staff ID - D3
Date - D4

Activity group name in column A (starting from row 8)
Activity type in column B (merged with C and D) (starting from row 8)
Activity time in columns E:GV (starting from row 8). Usually, there is none or only one value in whole range (e.g. E8:GV8). But sometimes there are two values.

Customer ID in row 6 (value appears only if time was reported in E:GV range)
CC Number in row 7 (value appears only if time was reported in E:GV range)

It's all about transferring values from all daily sheets in all files (.xls) sitting in folder C:WADFinal to one simple table (WAD_Consolidation_file.xls, sheet "Consolidated") consisted of 9 columns: Staff ID, Role, State, Date, Activity Group, Activity Type, Minutes, Customer ID, CC Number.

Additional note if two values exist in the same row they should be copied as two separate entries to consolidation file.

View 9 Replies View Related

Using Index / Match Function For Two Column Match Values

Aug 28, 2012

Basically where the columns say 2011 or 2012 AND 1, 2, 3. I want to be able to have it index the number below based on the GL number on the left and both the year and period on the top. I think that you can do with using the sumproduct function with the binary, but the computer is a little dated and it takes a while to run those calculations.

2011
2011
2011
2012
2012
2012

[code].....

View 5 Replies View Related

Force Focus Back: Pop Up A Userform To Provide Instructions And Collect Some Additional Data From The User

Feb 18, 2009

I'm working on a spreadhseet which has several macros that can take up to 5 minutes to run. When the macro(s) complete, I pop up a userform to provide instructions and collect some additional data from the user.

The "problem" is that when the user moves to another application to do other work, the user form remains hidden behind the active app. I've added some text to the status bar to let the users know the processing is done, but they would like something more obvious - so, is there any way I can force focus back to Excel?

View 2 Replies View Related

Inserting An INDEX,MATCH Function Into A HYPERLINK Function Instead Of Cell Reference

Mar 20, 2009

Another interesting dilemma to solve. Using this formula:

View 2 Replies View Related

Nesting Index / Match Function Within Vlookup Function?

Dec 3, 2013

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.

View 2 Replies View Related

Mapping Account . How To Add Additional Data In To Account#

Aug 21, 2009

I have two sheets. First contains Department # 0002549, and the other one contains 5 more digits(#02001 0002549). I will need to find the exact match for the first sheet to contain all 12 digits. THey are all in different order, so I probably have to match them first then add whatever the first 5 digits number. How should I tackle this issue? I was thinking to use vlookup but how could you use vlook up if only last 7 digit matches and add addtional digits to exisiting number?

View 9 Replies View Related

Match Function To Look At Max Value That Is Less Than Certain Value?

Dec 26, 2013

I'm trying to use match function to find the max value that is less than a given value. look at my file. I think it should return F as the value, why is it returning G?

View 11 Replies View Related

Match Function..?

Dec 17, 2009

=MATCH(9.999999E+307,B12:Y12)-1

=MATCH(9.999999E+307,B18:Y18)-1

So what is this pulling and how does it change depending on the data?

View 2 Replies View Related

Match Function

Nov 23, 2009

I'm using the match function to tell me in which column the first positive/non-zero number appears. However, the formula is returning the # of columns in the range. Here is what I'm using:

=MATCH(1,H6:CA6,TRUE)

I'm getting 72 back as a result, which is the number of columns between column H and CA.

Any ideas why this is the case?

View 10 Replies View Related

UDF Match Function

Feb 10, 2010

I have the below code sent in by a MR Excel forum user

Public Function BigMatch(rngIndexColumn As Range, varMatchValue As Variant, rngMatchRange As Range) As Variant
Dim lngCol As Long
Dim rngCell As Range
lngCol = rngIndexColumn.Column

For Each rngCell In rngMatchRange

If rngCell.Value = varMatchValue Then

BigMatch = Cells(rngCell.Row, lngCol).Value

End If

Next rngCell

End Function

when using the BigMatch Function = BigMatch(any cell in the column you want to pull from, the value you want to match, where you want to find that value)

I am using 2 different sheets, Technology & Lessons learnt: when I try the BigMatch Function

=BigMatch(Technology!C17,'Lessons Learnt'!A46,Technology!W17:AD27)

Instead of returning 'Technology'!C17 its returning the value from Lessons Learnt C17,

View 9 Replies View Related

Using Match And If Statement In Function

Feb 13, 2014

I have a very long function I created. But I need to edit it in a matter I have never done before.

Below is my function and i will like to add a new condition to it.

IF the text value in cell C7 is equal to any text value in Workbook2 B10:K10 and the value underneath that cell (I.e if B10 then B11 etc..) is Deleted then I want to have one of the conditions of my function below display "Deleted".

=+IF(AND(E7<=0,J7<>0,Y7<=0,AO7<=30),"Check Inventory",IF(AND(E7<=0,J7<>0,Y7<=0,AO7>=60),"Delete Listing ",""))

View 2 Replies View Related

Address And Match Function Together

Feb 23, 2014

Using the address function to find the exact cell of a unique string text.

I'm using the following command to find the address of a string i have in a different cell.

=ADDRESS(MATCH("*$H$1*",A:A,0),1)

The string text is in cell H1, H2, H3.......(or on another sheet). The goal is to find the address of a cell containing the unique string text from cell H1, H2, H3.....(that entire column) in a sheet with 13,000 lines. Basically, searching for a specific cell address of a string text referenced from another cell.

View 2 Replies View Related

Replace #N/A In Match Function

Aug 1, 2007

=IF(ISNA(INDEX($D$3:$D$100,MATCH(G19,IF($C$3:$C$100="DF",$A$3:$A$100),0)),"",(INDEX($D$3:$D$100,MATC H(G19,IF($C$3:$C$100="DF",$A$3:$A$100),0)))

what i'm trying to do is to replace #N/A with blank. Taken the above from vlookup examples but does not seems to work.

View 9 Replies View Related

MATCH Worksheet Function

Dec 12, 2007

MATCH("b",{"a","b","c","b"},0) returns 2

how about if I would like to have the return result as 4 since I have another "b" within that same array?

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved