Index / Match Search In 3 Separate Workbook

Sep 29, 2011

I have been working on this 2 weeks and have finally come to the conclusion. I am trying to use index/match to find an article number in any 1 of 3 attribute files workbooks. I have been am using Vlookup, but the Master attribute file workbooks DO NOT list the article number in column A (but list them in F) and therefore I have been creating an altered copy every month to place the article numbers in column A so VLOOKUP would work. Index/Match would be a more efficient look up formula as I could link directly to the Master Attribute files without altering the workbooks.

Below is the formula I have been using that looks in 2 attribute files workbooks

=IF(ISNA(VLOOKUP(A8,'C:Recon Workbook FilesService FilesAttributeFiles[TireAttributes.xlsx]TireAttributes'!A:C,2,FALSE)),
IF(ISNA(VLOOKUP(A8,'C:Recon Workbook FilesService FilesAttributeFiles[PartAttributes.xls]PartAttributes'!A:D,2,FALSE)),"",
(VLOOKUP(A8,'C:Recon Workbook FilesService FilesAttributeFiles[PartAttributes.xls]PartAttributes'!A:D,2,FALSE))),
(VLOOKUP(A8,'C:Recon Workbook FilesService FilesAttributeFiles[TireAttributes.xlsx]TireAttributes'!A:C,2,FALSE)))

Below are the three (the new formula would incorporate a 3rd attributes workbook "Other" to the search criteria) Index/Match formulas I would like to "Tie" together to find the article number in any of the 3 attribute files workbooks and return column A if the article number is present or nothing if the article number is not present in any of the 3.


[Code] .....

View 9 Replies


How To Automate Search And Match Function Between Two Separate Workbooks Using VBA

Nov 16, 2011

I'm trying to automate a search and match function between two separate workbooks using VBA and am having some problems. I'm using the following line:

search_results = Application.Match(temp, Range("E1:E900"), 0)

which works fine. now the problem is that when i'm searching for lets say "ABCD" in one of the workbooks, if there is an entry "ABCD " (with a space at the end) it says that the entry doesn't exist. is there any way of correcting it such that it would always neglect the space at the end if it exists? i tried using -1 instead of 0 and it messes up, so i'm not sure what to do.

View 2 Replies View Related

Index/Match Cross Search And Extract

Dec 2, 2009

sheet 2
column C is list of chemicals (to give context)
column A is a list of the publication numbers they apear in
column B is GOING to be the list of publication titles they are in

sheet 3
column A is a complete list of every publication number in our library
column B is the corresponding publication title

what i would like to do is extract the title from sheet 3 and input it into column B sheet 2 corresponding to whatever pub number is listed in column A of sheet 2

the code i have so far is in column b sheet 2 "=INDEX(Sheet3!B:B,MATCH(A2,Sheet3!A:A,0))"

now the problem i have is this works perfectly when only one pub is listed in a cell in column A sheet 2 but when there is more than one pub listed (ie "pub number" next line in cell "another pub number") it gives a N/A result.

is there anyway to call up multiple pub titles from the multiple pub numbers within one cell

View 5 Replies View Related

How To Use Index Match To Search For Value From Multiple Sheets

Sep 3, 2012

I have a set of values in column A in sheet 1 and a compilation of some of the same set of data in column A in sheet 2. Using the index match function, I have been able to list the corresponding values in column B of sheet 2 onto column B of sheet 1 (for those values which are common to both sheets - matching occurs between column A in both sheets). However, I now have some of the set of data in sheet 2 and the rest in sheet 3 (again, in column A). Is there any way of using index match to search in both sheets and index the corresponding value from column B of the sheet containing the matched value in column B of sheet 1?

I have tried this by nesting the index match functions for each sheet into two separate IF arguments but haven't had any luck. Is there a better way of doing this?

This is what I have currently tried in Cell B2 of sheet 1:

=IF(INDEX(Sheet2!$B$2:$B$3001,MATCH(A2,Sheet2!$A$2:$A$3001,FALSE),1),IF(INDEX(Sheet3!$B$2:$B$3001,MATCH(A2,Sheet3!$A$2:$ A$3001,FALSE),1)))

Excel keeps saying this formula has errors but I have no idea as to what changes I need to make to get the formula to model my situation. Then again, I'm not even sure if this is the approach I should be taking to create a formula that models my situation.

View 1 Replies View Related

Index Match Or Array Search Function

Jul 30, 2014

Here is the table I am working with

Adj Close
Semi-Annual Yield
Theoretical Bond Price
Total Value

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

The Relevant columns would be "Date" column A cell 1, "Total Value" column L cell 1, "Peaks" column M cell 1, and "Troughs" column N cell 1. What I basically want to do, in the next column, column O, is this: If cell N# = "Trough" find the previous M cell = "Peak" and take the L cell in that "Peak" row and subtract the L cell in the Trough row from it (like =(L4-L9)/L4)). Then in column P, if the percentage drop was 5% or greater, I want to find the days between the trough we are looking at, in cell n10, and the NEXT Peak, cell M11, using the corresponding dates in Column A.

View 3 Replies View Related

Retrieving Nth Results From Index Match Using Wildcard Search Term

Jun 9, 2014

I am building somewhat of a rudimentary search engine that looks for a name, address or unique identifier, using an Index Match formula that searches with a wild card:


"Name": is the defined name for column C on a separate sheet.
F3: is "search field"

The current formula (above), only returns the first match. The source data is only 927 lines so not overly large. I would like to have an additional page that will show the potential results from the wildcard search via a formula. Therefore needing results; 2, 3, 4, 5 etc. from the Index Match. I have searched high and low for solutions but I have found none in regards to retrieving multiple results from a wildcard search, only on a specific term.

View 3 Replies View Related

Offset / Index / Match - Using 3 Functions To Search For And Return Values From Data Sheet

Jan 19, 2014

Trying to grasp the concept of using these 3 functions to search for and return values from a data sheet.

The attached spread sheet has performance data for a group of employees.

What I need to do is find a particular employee then return a value for one of the category's.

For instance, I need to find "10TE03 ANGIE HOLLIS" Parts Usage on color or cell C10 in the attached sample.

Sometimes new category's are added to column A adding to the number of rows so a simple offset is not reliable.

Once I get that working, I then need to use a named range to total and average different data points for groups of employees by teams.

Maybe Offset-Index-Match is not even the way to go here?

View 7 Replies View Related

Formulas By Using VLOOKUP, INDEX, MATCH, INDEX&MATCH Separately

Oct 8, 2009

I have this table




As you can see, the number I has a,d,and g, II has b,e,and h, and III has c, f, and i

I want to make formula that if I make the input g it would return I, f would return III, and c would return III, and so on

I want to make four formulas by using VLOOKUP, INDEX, MATCH, INDEX&MATCH separately.

View 9 Replies View Related

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

Counting Number Of Times INDEX / MATCH Finds More Than 1 Match?

May 23, 2014

We know how INDEX/MATCH works, and it's very nice. I attached a COUNTIF to it to count how many times the index finds itself on another table; if it doesn't find itself, then it goes blank. However, this time I need to count how many times it finds a certain string condition in the other table.


The columns that need to be filled are shaded in dark pink.

View 5 Replies View Related

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

Separate Workbook By Value In Column Into Separate Workbooks

Feb 25, 2013

I have 10 very large workbooks that are all setup in the same format. In column Z is a numerical value from 1 to 83. I have been trying to filter the sheet and then copy one at a time from 1 to 83 but that takes a LONG time especially when there is 10 workbooks to do.

Is there anyway I can run a function or macro or something that would just automatically look down the column Z and put each row into a it's own workbooks?

I have attached a sample of what the workbooks look like right now.


View 4 Replies View Related

VLOOKUP / INDEX And MATCH To Return All Values That Match

Feb 4, 2014

I have two sheets of data , sheet A and Sheet B.

Sheet B contains a column called "Name" and for each name, and for each name there are corresponding numbers. In sheet A, I have a "list of interest" in column A. What i want to do look through the data in sheetB to find a match from the list of interest and return the corresponding letters, located in column A.

I have filled out the first two rows of results that should be returned as an example.

One idea i had was to put a vlookup formula in each column result 1 to result 6 so i can catch all 6 "Serves" columns from column B, but there may be duplicates in the serve columns and vlookup only reports the first match.

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



View 5 Replies View Related

INDEX / MATCH Multiple Ocurence Match Values

Jul 11, 2008

INDEX/MATCH multiple ocurence match values needed

View 9 Replies View Related

Lookup/Index-Match-Match Using Segments

Jun 10, 2009

Is it possible to return a value or a sum of values using segmented lookup criteria. The Sample 1 spreadsheet (attached) explains it better.

View 10 Replies View Related

Match Upper Closest Value Using Index-Match

Jan 15, 2013

I couldn't resolve the formula of Index-Match for getting the values that matches the following criteria

1) Match the "Category" of Table 1 to Table 2

2) Match the "value" of Table 1 to Table 2, if value is not present in Table 2 take the upper closest value.

Once the above conditions are met, Take the Result from Table 2

Table 1 is for Input and Table 2 is for Database.

Please note that Data in Table 2 should not be sorted in any way. I tried to use the -1 option of the Match function for upper closest value but it didn't work out.

Have a look at the file attached : IndexMatchUpperClosestValue_AlongwithExactMatch.xlsx

View 3 Replies View Related

Adding A Third Match To An Index Match Formula

Oct 6, 2009

I currently have a spreadsheet with two sheets. The first is my reporting sheet and the second is called Stores. I currently am using the following formula in the reporting sheet:

View 3 Replies View Related

Sum If W/ Index Match On Vertical And Horizontal Match

Sep 27, 2007

I am looking to sum the values found at the intersection of multiple values based upon a vertical and horizontal lookup. The formula I am trying is as follows (which results in #Value):


View 9 Replies View Related

Search A Sheet For A Match And Then Copy All The Cells To The Right Of The Match

Jul 13, 2009

I need a macro that can search a sheet for a match and then copy all 7 cells to the right of the match. I have attached an example of the sheet that will serve as the database to search, and a userform example that will be similar to the userfrom that will display the copied cells when a match is found. I plan to copy and paste the 7 cells to a different sheet so that the userform can display the results with the control source property. I do not need a way to add to this database. I know very little about searching a database so.

View 6 Replies View Related

Hlookup And Match Or Index And Match?

Apr 6, 2009


On my worksheet 1, I have two dropdown boxes: B7 (that gives the building name) and B11 which gives the Type of Commission (Renewal, Expansion, New). B 20 is supposed to automatically select the percentage based upon what B7 and B11 select. E.g. B7 = House, B11 = Expansion then B20 should be 3.

This is my formula.
=HLOOKUP(B7,'Building Details'!$A$2:$R$24,MATCH(B11,'Building

View 3 Replies View Related

How To Separate Row Data From One Workbook Into Separate Workbooks Based On Cell Data

Apr 23, 2014

I need to separate row data from one workbook into separate workbooks based on cell data. Currently I filter the data, copy and paste it to a new workbook and save it. It's a tedious process and was hoping to find a way to automate it. I have attached a sample file. In this instance, I would like a separate workbook for the filtered data in Column 1 and then all the row data gets copied to new workbook. So all of Pennsylvania data would get copied to new workbook, then all of the Michigan data gets copied to a new workbook. I have also attached an example of the end result that I need.

View 9 Replies View Related

Copy A Range Of Cells In Another Workbook Opened In A Separate Instance Of Workbook

Feb 11, 2010

I m not able to use the standard Excel Paste Special function when I copied a range of cells in another Excel workbook opened in a separate instance of Excel. Instead, Paste Special thinks that I have copied some non-Excel objects and gave me the Paste As options. This is not the case if I open both workbook within the same instance of Excel. Could you share with us if there is a trick to trigger the normal Paste Special options in such situations (without having to invoke Macro procedures)?

View 2 Replies View Related

Combining Two Values In Two Separate Cells To Make A Cell Reference Or Index Refer.

Jul 15, 2009

I want to use a value in one cell as a row designation, and a value in another cell as a column designation. Ultimately, the values will be text which will refer to row and column headers. What formula would allow me to do this? example:

A1 contains B
B1 contains 2
B2 contains "tribbles"

An imaginary function might go like this........

View 3 Replies View Related

Search A Value Then Return Value From Separate Sheet

Mar 26, 2014

I have a sheet named formula the column highlighted in orange will be used in searching to the other sheet which is the table, I try to used the formula VLOOKUP but it didnt work.

View 5 Replies View Related

Extracting Information From A Workbook And Saving Into A Separate Workbook

Feb 21, 2014

i have a workbook blank template for my colleagues to fill in, they fill in this blank template and save it under the serial number of the item they are creating. However a lot of errors exist when they input the serial number inside the workbook where the serial number is a duplicate of a former serial number.

Anyhow i was wondering is there any way when they input the serial number into the worksheet that i could get it to save in a separate workbook and then have conditional formatting to check those serial numbers in the workbook to see if they match?

View 4 Replies View Related

Create New Workbook With Separate Sheets From Data In Another Workbook

Jul 16, 2012

I have been tasked with creating a macro which creates a new workbook wherein each sheet contains the information for one site from the active sheet. The active sheet already has the values sorted by the site such that all information needing to be copied from the active sheet into the new workbook is together.



There are 8 different sites on the active sheet: ATL, CEN, DAL, HAR, JAS, JCK, VIS, NOV

The macro needs to find the range for all of the data of each site and copy/paste that data into a new workbook such that ATL would have its own sheet, CEN would have its own sheet, and so on. The data ranges from A:R.

So, for example, the macro would find that the last row with ATL in the "B" column is 6095 and would then copy A2:R6095 and insert that data into the new workbook under Sheet 1.

I had some code that I had adapted to select the range for each of them, but the code loops through the entire sheet (which is 44,307 rows long) for each site making it a quite clunky and very slow step in an even longer macro. Since the data is already sorted, I know there must be a way to have the macro stop searching when it reaches data not equal to the data the row before, however, my experience with VBA is limited, and I have been unable to find a solution. Also, the data does not have to be conserved after being sent to the new workbook, if that would speed up the macro.

View 5 Replies View Related

How To Create Search Macro Button That Allows To Search In Multiple Worksheets In One Workbook

Oct 11, 2011

Im am trying to create a search marco button that allows me to search in multiple worksheets in one work book. I came across this CODE the first part of it works. It pops open user input box and ask for the word that i would like to search but the this error message pops up Runtime error1004 Method 'range" of object'_Global'failed and i dont know what to do

Private Sub SearchButton_Click()
SearchString = InputBox("Enter Search String", "Search")
If SearchString = "" Then Exit Sub
For Each c In Range(myRange)
If InStr(LCase(CStr(c)), LCase(SearchString)) Then


View 1 Replies View Related

Search Function (locate Data) Search All Sheets Within The Workbook

Sep 14, 2009

Using the search macro code below, could someone please help to add in more codes what I'm currently using, and also where to insert it. The Search function works well for what I need and it helps me to locate data. When using the search function somehow it search all sheets within the workbook but I only want it to search an array of sheets when using this macro that is needed to complete the task for what I'm after.

Public Sub FindText()
'Run from standard module, like: Module1.

Dim ws As Worksheet, Found As Range, rngNm As String
Dim myText As String, FirstAddress As String, thisLoc As String
Dim AddressStr As String, foundNum As Integer

myText = InputBox("Enter the text that you want to search for:", "Start Search!")

If myText = "" Then Exit Sub...................

View 9 Replies View Related

Using 2 Separate Key IDs To Match A Variable?

Jan 6, 2014

What I need is to lookup the matching 2 key IDs (i.e., ISIN and Firm_Quarters) in Sheet2 and then return the variable (i.e., market cap) in Sheet1. I have done one manually highlighted in yellow.

View 4 Replies View Related

Copyrights 2005-15, All rights reserved