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:
=INDEX(Name,MATCH("*"&F3&"*",Name,0))
"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.
Col. A = Article, Col.B = Part Number, Col.C = Location
I am currently using an index & match formula to create a search function. For example if I want to know what article number PLU547 has then I use
=INDEX(A3:A12,MATCH("*"&A19&"*",B3:B12,0))
This use of wildcard is fine until it comes to part number searches such as PLU54 (which is not in the list). In this instance the formula returns the first article number from the list that starts with PLU54. However, part number PLU54 is a code in its own right and I fear that using the wildcard approach needs a modification to limit the returns.
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.
In a nutshell, I have data that needs to be sorted but some intermediate to advanced (to me) excel formula needs to be used to sort the ranking properly. I've already used index, match and large but it does not suffice.
I've been trying to edit my index formula to only count an order number once even if the criteria for that result appear more than once. Attachment 324723
I have a table with Dynamic Headings and Expanding rows, that's setup like below: The formula in the Qty column would produce a "0", if the result of the Index Match has a blank. For example in a different table the reference cell would have "Heading3". Therefore the results in the Qty column would be like example below. The products in the table below are like 0's and 1's to trigger the formula to calculate with the variables, or give a zero. If the Index Match found "Heading2" the results would be based on cells under "Heading2 Column" etc.
Currently i am using index and match in excel but however i am unable to get the result tt I want.. what i need is
Data to check with item master whether all 3 (article, color code and size) matches and if it doesn't match, which of the field is wrong. (eg: wrong color code, wrong size or article don't exist).
[Code] .......
Above is my item master
Data to check
[Code] ........
Example case: 1
Checking first row of data check
Article number: 2-50660147
Color code: 6
Size: M
Result: "Wrong size"
But however, it returns as wrong size as it only reads the first time the article number appear in the item master and does not recognize the second time the article number appears with the correct color and size.
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
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
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.
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:
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.
Date Open High Low Close Volume Adj Close Semi-Annual Yield Theoretical Bond Price Index Coupon Total Value Peaks Troughs
[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.
I have a worksheet named "List" with static values in columns A & B. I want to search all the other worksheets in the same workbook for the one that matches the value I entered in column B on the "List" sheet. The cell on the other worksheets with the possible match is B5. When it finds a match, I want it to take the data from cell B3 on that worksheet and paste it in Column C of the "List" sheet on the corresponding row (all rows in the "List" sheet have a different value in Column B).
In other words, when "List"b7 = worksheetb5 , then worksheetb3 is pasted to Listc7.
I am trying to use a lookup formula to search for a word in a column, and return the value of the column directly to its left. I know that this cannot be done using the vlookup function, and I am not able to move the columns around. Is there another possibility? In plain English then, I would like cell E2 to look up the value of D2 in column B2:B4 and return the relevant value in column A2:A4.
I have a 14 000-item list of product codes that are generated from different attributes. What I am trying to do, is to create a cover sheet where the user can select attributes from dropdown lists and get the first matching product code. I have tried using index/match, but I am struggling with wildcard lookup.
Example of generated product code:Cell A1: ABX**J
Here, AB is the product group, X is the pressure class, ** is material (which is, for the example, unknown), and J is the end connection. What I now want to do, is to look in the long list of codes and find the first match:
I am looking to find the rider names of the first top 3 position for each stage in Results Page. The data comes from Stage Results. Initially I used Index and Match but to no avail.
I have a spreadsheet where there are lines of different Wage Types but only certain combinations are needed. For example I have Pay Area 1234 which needs all wage types except those beginning with a 9.
To do this I have set up Sheet1 to look like this:
12341 12342 12343 12344 12345 12346 12347 12348
and not 12349 as I do not require this type.
The problem here is I need to add a wildcard to the end of these so it finds anything that starts with 12341 etc.
Sheet1 contains the personal details of employees and I have concatenated the Payroll Area and Wage type in column D.
Code:
Sub deleterows() Application.ScreenUpdating = False Set MySheet = Sheets("data") LR = MySheet.Cells(Rows.Count, "D").End(xlUp).Row For i = LR To 2 Step -1
I have numerous formulas that use a macro with an indirect cell reference, for example: INDIRECT(macro(A21))
I am moving these formulas to a different workbook and I need them to still refer to items in the old sheet. I accomplish this by using this formula:
INDIRECT(CONCATENATE("OtherSheet.xls!",macro(A21))) This works. However, I need to do a global search and replace for all of these formulas without deleting my indirect cell reference. I could replace macro with CONCATENATE("OtherSheet.xls!",macro but that would leave me short a ). Is there a way to use a wildcard to accomplish this? It does not seem that replace macro(*) with CONCATENATE("OtherSheet.xls!",macro(*))
I need to do a vlookup on C1 in range A1:B1 that will return B1 if C1 contains the string in A1. I've tried using the TRUE variable in the vlookup formula which does not require an exact match. This works for about 80% of my data but is not 100% accurate. Of course I've got a long list of data ... not just the 3 cells.
I need to get rid of the questions marks in the text on my sheet, but am unable to get my VBA code to search for an actual question mark and not the wildcard. I'm using the replace function as follows:
wsReport. Cells = wsReport.Cells.Replace("?", "")
1. What's the trick to searching for wildcard characters?
I'm trying to perform a search of over 15,000 entries using a wildcard. Sheet1 is a list of streets in the county where I live, and I would like to be able to type a part of the name that will return every instance of that name appearing.
I found a code which i have tried to adapt, but it is not working.
[Code] ....
The item being searched will be entered into TextBox1, and I'm guessing that the error I am receiving is in the .AutoFilter Field:=1, Criteria1:=TextBox1"*" line of code. This is the original sample I found onlne:
[Code] .....
where they were only looking for the letters "GB".
I'm having a hard time getting a MATCH formula to return the correct results. See attached workbook. I have a list of names and wish to look for the first 6 characters of each name in another list and return the row number that the 'MATCH' appears in. The formula I am having problems with is in column 'E' of the ' Analysis' sheet. I have entered the expected result in column 'F'.
I have a sheet with Names in it. I need to look those names up and find which org they belong to. The problem is the the names have many characters after them, and many names belong to a few orgs, with a the rest belonging to many orgs. (See below)
I have been trying to get a Vlookup to work, but since it can't go left, it won't look up the orgs that don't own at least three names. And I can't get the search to work on the names.
I would like to search for a certain pattern that appears in a cell. For example, I have certain cells that begin with a 7 digit project code (Ex: "1234567 - Project Red"). I was planning to use an IF inside a FOR loop and then do an action when I find the cell with the pattern. I thought I remember it being something like Format(########*) so that I have the wildcard on the end since there is more than just the numbers. how to make other patterns work such as Number-Letter-Number or Capital-Lowercase-Capital
