Index Match Not Returning Any Results?
Dec 3, 2013In the attached I have an index/match formula in B15 that I cant get to return any results not even a N/A#
Quick Quote Tool.xlsx
ADVERTISEMENT
Returning 2nd Occurrence Using INDEX And MATCH
Jan 20, 2014I am trying to find the 2nd occurrence in a range of text along 1 column and return the cell one to the left of that 2nd occurrence. I will also need to find the 3rd, 4th, 5th, etc occurences, down the column.
So far, I have a working formula to find the first occurrence and it looks like this:
=INDEX(INDIRECT(A2),MATCH("*SS*",INDEX(INDIRECT(A2),0,2),0),1)
How would I find the 2nd occurrence?
what if the formula looked like this?
how would I find it's 2nd occurrence?
{=INDEX(INDIRECT(A2),MATCH(TRUE,ISNUMBER(MATCH(INDEX(INDIRECT(A2),0,2),{"CF","LF","RF","OF"},0)),0),1)}
Sumif With Index And Match Is Returning Too Many Arguments?
Mar 2, 2014I am trying to sum the data in "details sheet" column H into "monthly sheet" based on the period and type match.
I am not sure where I am going wrong as the column references returned values that they should. However, the values in Col H from details sheet are not being summed up based on the criteria into monthly sheet.
I am getting zero amounts.
Index And Match Function Returning Same Values
Dec 15, 2009In cell G51 of sheet "My Overview", if two people have the same scores it pulls through one person twice in F51 and F52, how can i overcome this?
View 4 Replies View RelatedReturning Second / Third Etc Values Using VLOOKUP / INDEX / MATCH
Mar 1, 2014making a forumla that will return the second, third ewtc values using VLOOKUP or INDEX/MATCH. Attached is a dummy file almost identical to the one I'm working in. In the first sheet there's a dependent drop-down list with the values Sub1, Sub2 and Sub3. This corresponds with data in sheet two listed next to these names. The problem is when I try to populate the table below using VLOOKUP etc it just repeats the first row over and over rather than defaulting to the second and third row results
View 6 Replies View RelatedMatch And Index Function Not Returning The Correct Field?
Sep 29, 2012I am currently using the Match and index functions to search for specific criteria and return specific data based on that criteria. The problem is the formula is returning the first value at the beginning of the month rather then the end of the month value that I am asking for. I have my criteria set to zero so it should be a perfect match. with the live worksheet I have several tabs for different years. I want to take the end of month balance on each of those year tabs for each month in each fiscal year and add them together. My formula only has the one month because I am unable to get it to work much less adding in all of the years information. I have the employees choosing EOM for the last transaction posted during that month and that calculates the month number in the field next to it. I was thinking I could use the match to look for a specific month in that column and return the balance adjacent to it at that point.
View 7 Replies View RelatedIndex With Multiple Match Formulas Returning Undesired Value?
Nov 20, 2012I am building a tool for work, and in one of the sections of the main worksheet, I would like the cells to pull specific data from another worksheet that contains the results of a web query. The web query pulls a table of data, with a double header (rows 3 and 4). One column header is for the date and the other is for the time the data below corresponds to. There are also row titles along the left hand side.
I need a formula that will find the column that meets two conditions:
1) Today's date
2) A specific time (referenced from a cell on the main sheet)
It must then pull the data from the cell in that column that is in the row marked "Total".
Here is an example of the data set from the web query:
Excel 2010BCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATA
UAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBT3
Total19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-
Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-
Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-
Nov22-Nov22-Nov22-Nov22-Nov22-Nov22-Nov22-Nov22-
Nov23-Nov23-Nov23-Nov23-Nov23-Nov23-Nov23-Nov23-
[Code]......
I have done quite a bit of looking on the web and tried out several different formulas/variations. I think I'm getting close, but just can't get it quite right. Here are a couple that seem close and their results:
=INDEX('Rodeo Data'!$D$5:$JS$50,MATCH("Total",'Rodeo Data'!$B$5:$B$50,0),MATCH(J3,'Rodeo Data'!$D$4:$JS$4,0)*AND(MATCH(TODAY(),'Rodeo Data'!$D$3:$JS$3,1)))
This one correctly finds the date and "Total" row, but seems to ignore the MATCH(J3,'Rodeo Data'!$D$4:$JS$4,0) section, which is designed to find the specific time in row 4. It returns either the "Total" row value from the first or last column with the correct date depending on which match type argument I use.
=INDEX('Rodeo Data'!$D$5:$JS$50,MATCH("Total",'Rodeo Data'!$B$5:$B$50,0),MATCH(TODAY()&O3,'Rodeo Data'!$D$3:$JS$3&'Rodeo Data'!$D$4:$JS$4,))
This one returns the standard "#N/A" error message.
I am using Windows 7 and Excel 2010. "Rodeo Data" is the name of the sheet that contains the web query data I have included. The J3 and O3 references are cells in the main worksheet that contain the time I want the match function to find.
The desired output from the formula would be if I referenced it to today (20 Nov 2012) and 19:45, it would return the value of 65,826.
It may also be worth noting that the number of columns and rows in the data set is dynamic.
Can Index Or Match Return Multiple Results
Nov 27, 2006I'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.
Index IF Match Formula / Returning Room Number That Matches The Hours Used
Mar 21, 2014I am trying to find a formula that populates a room number based on the number of hours used. I have a chart that is by building but multiple rooms within each.
Where I am having trouble is when there are more than one room with the same number of hours used then sometimes the formula populates the wrong room number.
Attached is my example spreadsheet of my chart. Book1.xlsx
I have shown what I need, what I have tried and a blank space to show where I need it to go.
Sorting Of Results Using Index / Match / Large And Other Formulas?
Jul 16, 2012how to return the top 5 results in a set of data.
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.
Index / Match Function To Count Criteria Results Once?
Jun 11, 2014I'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
View 7 Replies View RelatedExecute Formula Based On Index Match Results?
Oct 21, 2012I 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.
x
y
z
[Code]...
Index And Match To Check Data And Provide Exception Results
Mar 25, 2014Currently 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.
Retrieving Nth Results From Index Match Using Wildcard Search Term
Jun 9, 2014I 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.
Formulas By Using VLOOKUP, INDEX, MATCH, INDEX&MATCH Separately
Oct 8, 2009I have this table
.......A.....B.....C....D
1.....I......a.....d.....g
2.....II.....b.....e.....h
3.....III....c.....f......i
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.
VBA Autofilter Not Returning Results?
Jul 6, 2013I have a large table which I am filtering 3 times. There should be results but the final VBA filter does not return them until I manually apply the filter. The custom filter is correctly populated with the criteria. The code that I am using is as follows:
VB:
Cells.Select
Selection.AutoFilter
Selection.AutoFilter Field:=9, Criteria1:="<>", Operator:=xlAnd
Selection.AutoFilter Field:=10, Criteria1:="=INV", Operator:=xlOr, Criteria2:="=CRD"
Selection.AutoFilter Field:=21, Criteria1:=">=" & DTPicker1.Value, Operator:=xlAnd
The first two filters work fine. The last does not. Oddly it works on some date picker values and not others, even if there are actual results and as mentioned earlier if I stop the program running and apply the filter (without typing anything else) it works fine.
IF Formula Returning Two Results?
Feb 21, 2014I am using this formula. How can I get it to pull 'Sales Materials'!$F17:$F69' and display along with 'Sales Materials'!$B$17:$B$69 when my logical statement is true.
=INDEX('Sales Materials'!$B$17:$F$69,SMALL(IF('Sales Materials'!$E$17:$E$69="YES",ROW('Sales Materials'!$B$17:$B$69)-16),ROW(A1)),1)
I've tried using * but I think something is going on with "-16" which I was told to you use to account for headers.
Returning Multiple Results Using IF
Aug 12, 2009I'm trying to have an IF function analyze a sheet and then return a list of names that return a True
=IF(AND('LTV Watcher'!L2="Yes",'LTV Watcher'!M2=""),'LTV Watcher'!A2)
and it returns:
John Doe, LLC
however I'd like it to function to analyze a range of cells:
say L2:99, M2:99, and A2:99
VLOOKUP / INDEX / MATCH Function: Match Data From 2 Independent Sets??
Oct 8, 2009I 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 RelatedCounting Number Of Times INDEX / MATCH Finds More Than 1 Match?
May 23, 2014We 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.
sampleexcelhelp.xlsx
The columns that need to be filled are shaded in dark pink.
Index And / Or Match Formula With Multiple Match Required To Return A Value
Jan 21, 2014See 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?
Returning Multiple Results For One Lookup Value
Jan 28, 2009I have been searching all day about this topic and while there are many "solutions" none of them fits my criteria and I can't figure out how to tailor it.
I am using a very basic data sheet to populate a purchase order. I want to be able to search a style and have all the data (color, units and price) automatically fill in upon entering the style number, I have been using vlookup but this only works if there is one color per style.
I have found different lookup functions that give the value +1 when the data is sorted but it returns the value even if it doens't fit the search criteria, I only want to return the second value if it correlates to the initial lookup
Returning Results Via Filter To Next Sheet
Feb 25, 2009how to return data to a second sheet if data selected via a filter.
i have attached spreadsheet i am working on and looking to make this as easy as possible
on sheet "data" cell f2 im looking to return all goals scored by hull
on sheet "data" cell f3 im looking to return all goals conceded by hull
on sheet "data" cell f5 im looking to return all goals scored by hull at home this i will need to do by selecting hull in "home team" filter hull
on sheet "data" cell f6 im looking to return all goals conceded by hull at home this i will need to do by selecting hull in "home team" filter hull
on sheet "data" cell f8 im looking to return hulls last 6 games as over and under which in this example will return the last 6 rows 27-32 and will read 4 under 2 over
on sheet "data" cell f9 im looking to return hulls last 6 games at home as over and under which in this example will return the last 6 rows 22/23/26/27/29/32 and will read 3 under 3 over
VLOOKUP / INDEX And MATCH To Return All Values That Match
Feb 4, 2014I 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.
Using Index / Match Function For Two Column Match Values
Aug 28, 2012Basically 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].....
INDEX / MATCH Multiple Ocurence Match Values
Jul 11, 2008INDEX/MATCH multiple ocurence match values needed
View 9 Replies View RelatedINDEX Array Formula Returning Same Value?
Feb 18, 2014I know I can do this as I have already succeeded once but since moving a few things on my spread sheet the formula has gone wrong and I can't correct it.
I have one sheet with many different lines on and another I would like to select certain rows with the same reference number in column A. Below is the formula I am using
=IF(ISERROR(INDEX(Lines!$A$2:$J$200,SMALL(IF(Quote!$AL$4=Lines!$A$2:$A$200,ROW(Lines!$A$2:$A$200)-
MIN(ROW(Lines!$A$2:$A$200))+1,""),ROW(Lines!$A$2:$A$200)),COLUMN(C1))),"",
INDEX(Lines!$A$2:$J$200,SMALL(IF(Quote!$AL$4=Lines!$A$2:$A$200,ROW(Lines!$A$2:$A$200)-
MIN(ROW(Lines!$A$2:$A$200))+1,""),ROW(Lines!$A$2:$A$200)),COLUMN(C1)))
what my error is as it is returning the same value for me each time.
Max Index Lookup Returning Two Cells?
Oct 19, 2007I have a formula I need to modify.
In column b I have a list of items by number, each one is unique
In column c I have a list of items by number. In this column a item may not be listed or listed 1000's of times.
In column d I have a list dates for each of the items listed in column c
In column e I have a list of prices for each of the items listed in in column c
The idea with the orginal formula was to look up the item and return the date
=MAX(INDEX(($C$2:$C$39999=B2)*(D$2:D$39999), 0))
Now I need to get it to return the most recent date from column d and the dollar amount right next to it from column e. How would I do that?
I trying to return the most recent date and price for a item.
I thought about breaking this into two formulas and having the second formula read the date that was returned from the above formula and the item number from column b and returning the value from e, but got stuck. I was thinking a lookup function with two criteria.
VBA Recordset Not Returning Updated Results From Query?
May 9, 2012I have a function that loops through sheets and opens a query and copys the information to a sheet. I have gone in the access db and updated the query. Yet everytime i call it in excel it returns the old data. I have gone into access and verified what data I should get and I just don't get it.
I pass in the query name and the sheet name into qryName and sheetName name.
Code:
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
Dim strFilePath As String
[Code].....
Returning Advanced Filter Results Into Row Not Range
Jun 7, 2006I am trying to create an advanced filter that filters my results into a row.
e.g.
Shane
Shane
Tom
Tom
Paul
Mike
would return
Shane Tom Paul Mike
I have tried changing this piece of code
Range("M1:M300").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Columns("M:M"), CopyToRange:=Rows("25:25"), Unique:=True
The problem is with
CopyToRange:=Rows("25:25")
I have tried using Range instead of Rows but I am not having any joy.