make an adjustment to my Formula so that if the Index match locates a cell that is blank it returns a blank cell rather than a 0. I know we need to add in =IF(iserror( but i am aving trouble..
View 5 RepliesI have an array formula which is working as expected returning the value of column K (offset by -1) if a number of criteria are matched.
[Code].....
The issue I'm having is there are occasionally duplicate values in column W and it's using the first value in its return, which isn't always correct.
Unfortunately due to the limited matches available the only way I can think to identify the correct value is by adding an additional criteria, which is that the correct value would always have a negative in Column X one cell above and to the right of where the value is being retrieved from. If there was no negative it would ignore this result.
Is there any way to add an additional criteria to the formula to stipulate that it must match a negative in column X but one cell above?
I have a rather large table that I need to return the column heading from.
Within the table I have a list of Names in the left most Column (A) and a list of Percentiles in the header row (row 2) (incrementing by 5%). By inputting the name and a cell value I am trying to return the column header.
Using an example;
5% 10% 15%
Bob 3.5 4.1 4.15
John 3.6 4.2 4.30
Chris 3.3 4.9 4.95
Sammy 3.7 4.6 4.7
Like I said, knowing the name and the cell value, I want to return the percentage value. So for example, Bob would be one input value, and another input value would be 4.1. I would want excel to return 10%.
I have found different suggestions using a combination of index/match but even using these functions I cannot get it to return the COLUMN value instead of a cell value.
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?
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.
I'm trying to set up my formula so that if there is no criteria match, rather than returning 0 it leaves the cell blank.
(I recommend skipping the below text and just opening the attached spreadsheet, instructions/what I'm looking for and trying to achieve and well laid out requirements and examples are in the spreadsheet and much more thoroughly explained then I can do below)
I'm setting up a table that grabs data from columns in a raw data spreadsheet.
For the purpose of the table I'm making, the formula needs to match up both the House, and the year/month from the raw data sheet, then if the two match and are side by side, return the value to the right either in the Plan Field or the actuals field depending on which field in the table the formula is in. If there is no entry or matching criteria for both the house and Month then it should leave the cell blank, not show a 0
So I have a SUMIFS formula that I'm trying to set up so that if there is no criteria match it leaves the cell blank.
So =SUMIFS(sum range, criteria& range, criteria&range), if no matching criteria can be found, leave cell blank, else if matching criteria found then return value from the sumrange.
This is the exact formula I am using:
=SUMIFS(INDEX('Raw Data'!$1:$1048576, 0, MATCH(Table!$B9, 'Raw Data'!$1:$1, 0)), INDEX('Raw Data'!$1:$1048576, 0, MATCH(Table!$A$8, 'Raw Data'!$1:$1, 0)), Table!$A$9, INDEX('Raw Data'!$1:$1048576, 0, MATCH(Table!$B$8, 'Raw Data'!$1:$1, 0)), TEXT(Table!C$8, "yyyymm"))
Yeah kind of hard to follow, so attached is a very detailed and well laid out spreadsheet showing the example of what the formula is, how it's working/what it's returning and an example of what i want it to return/look like, so please check out the spreadsheet, it explains it a million times better.
Essentially though i want the formula set up so that if there is no criteria match, rather than returning 0 it leaves the cell blank.
I have two active worksheets. The first one is called 'Chart' and the second is 'Values'. I have three columns of data. I would like to look in the first two, and return a value in the third. =INDEX(Values!$C$1:$C$299,MATCH(Values!$D$1:$D$299,LEFT(Chart!A1:A16091,6)&" "&LEFT(Chart!C1:C16091,2)),0) .........
View 2 Replies View RelatedI am very competent using the index match formula and have incorporated this into many spreadsheets to auto-fill for avoiding input errors. The objective I am attempting now is to retrieve from a list of 2800 entries the smallest return on an index match formula.
From the data below if my Index Match formula uses C1 to index and match my return is always a7 (-12.7) the first in the list, where my desire is a return of a19 (-14.11). To complex this problem I often have duplicate amounts that need to be listed when they fall into my parameters. I have figured that the Small function allows me to assign first smallest, second smallest, and so forth.
I prefer not to use vlookup for simplicity and error avoidance issues.
a b c
1 amount Reason for Movement 2250
2 -183.252150
3 -366.612150
4 -61.1 2154
5 -91.652150
6 -4.34 2450
7 -12.7 2250
8 -2.98 2250
9 -2.98 2250
10 -69.372452
11 -0.6 2451
12 -4.49 2250
13 -4.49 2250
14 -4.14 2250
15 -4.7 2250
16 -4.55 2250
17 -4.85 2250
18 -1.41 2250
19 -14.112250
20 -8.47 2250
How can I modify this formula so it retrieves the 1st matching value (like always), but then the 2nd match, 3rd, etc.:
{=INDEX($A$2:$D$6,MATCH(F2:$F$6&G2:$G$6&H2:$H$6,$A$2:$A$6&$B$2:$B$6&$C$2:$C$6,0),4)} sheet attached: Example.xlsx
how to go about this, as I always get confused with Index and Match. In my example, in col A I have the Beginning of a period, Column B is the End of the Period, and the amount to be matched is in Column D. Range A1 to E4 is the source data. Below that, starting from A9 to A37 are dates. I would like to match the corresponding value in range D2:D3 if the date in range in A9 : A37 falls between the period stated in A2:B3. I've listed how the values should appear in C9:C37.
View 2 Replies View RelatedWhat I want to do is, given the data in col. a, I want to supply a number(range) of strings which can be found in column a, whenever a string is found, which exists in given range, I want to write the string from the Key(col. c) in col b instead of the value from the string that matches a value.
See below for example of what I got working but not exactly what I was looking for
Col. A- Data
Orangegreen
Blackwhite
Blueyellow
Col. B - Result
Col. C - Keys
Orange
White
Yellow
I used
Code:
=INDEX(A1:A3,MATCH("*"&C1&"*",A1:A3,0))
This does produce a result, but not entirely what I am after.
This requires only one string to search for, and will in cell B1 result in a value of 'Orangegreen' as I can only supply one valeu to the function. What I want to do is supply the function with a range of values, and have those values outputtet in colb instead af the value of cells where the result is found.
I tried modifying the function to include a range, but that does not produce a valid result.
If the weight of a shipment is between a range e.g 3.5kg I need it return the higher cost. For England this would be 9.31.
COSTLook upWeight KGEnglandScotlandIrelandWales1131-3 (KG)7.687.687.688.144454-5 (KG)9.319.319.319.4566306-30 (KG)14.3014.3014.3014.4531315031-50 (KG)22.4222.4222.4223.5551517651-75 (KG)35.1735.1735.1737.04767610076-100 (KG)45.2645.2645.2647.62KGS3.5ORIGINEnglandCost
In column B I enter a date when a student attended tutoring.
In column C I enter the name of a student attending the tutoring.
In cell D4, I will be entering a student name
In cells D5:D55 I'd like a formula that transposes the dates (if any) that a student attended tutoring. So if a student has their name appear beside the dates ... 14-Feb, 22-Mar, 24-Mar .... then I'd like those three dates to appear in cells D%, D6 and D7.
How to formulate this ... I'm guessing it's going to involve an index-match formula.
in one column I am looking up the value Yes to return the row number.
=MATCH("Yes",C:C,0) in this case it returns a 2
I want to use this row number in a sum...
i.e. =sum(b2:b&x) where x is the row number from the formula above, but it just errors out.
I have a dataset of a few hundred rows, but will only provide a small sample of rows for this example.
I need INDEX/MATCH to return the correct values for the subset below.
Column A (Forecast Status) - Column B (Account Name)
Commit - Account01 (Row 1)
Commit - Account02 (Row 2)
Won - Account03 (Row 3)
Won - Account04 (Row 4)
Won - Account05 (Row 5)
Upside - Account06 (Row 6)
Lost - Account07 (Row 7)
Upside - Account08 (Row 8)
Won - Account09 (Row 9)
Commit - Account10 (Row 10)
In a new tab, I am creating a 'dashboard' view that will group each of the following:
Commit ONLY Accounts in cell A1 (Formula to be copied down to retrieve Rows 1,2,10)
Won ONLY Accounts in B1. (Formula to be copied down to retrieve Rows 3,4,5,9)
Upside ONLY Accounts in C1. (Formula to be copied down to retrieve Rows 6,8)
Lost ONLY Accounts in D1. (Formula to be copied down to retrieve Rows 7)
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.
I have a workbook that uses several indentification fields one for Job ID, One as a subID which occurs for every part of a job, i am currently using formula
View 5 Replies View RelatedI have a list of data that I want to average the number in column B when there is a match in column A
COLUMN A | COLUMN B
alpha | 100
beta | 200
gamma | 300
alpha | 400
beta | 500
What I'm looking to do is return the MEAN of values in column B where column A = alpha. Though, I would settle for returning a list of the values that match the criteria. Index/Match combinations don't seem to be helping me because they only return the first match, I'd like to return all matches.
I need to find a way to find an array in a tab to use for an index/match function I have.
this is what i would normally use: =index(tab_array,match($a2, tab!$a$1:$a$1000,0),match($B$1,tab!$a$1:$zz$1,0))
This formula would usually work fine when I know that within the tab, the array in which the row match is searching doesn't change. However, that array may change in the future, to say column H, without my knowing as it is a database that gets automatically populated from an upstream system.
Is there a way to search for an array within an index/match?
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.
currently using Excel 2007 with Windows Vista.
I currently have a worksheet where I want to input a date (G2) and a rank value (H2) ranging from 1 to 4. The header value (B1:E1) corresponding to the date (A2:A4) and the rank (B2:B4) should be returned to I2 (currently returns #NA).
Using formula: =INDEX($B$1:$E$1,MATCH(H2,INDEX($B$2:$E$4,MATCH(G2,$A$2:$A$4),)))
which I found under: Find row, find value, then return column heading
However, the above formula does not seem to work with my date order or recurring data values of 1 to 4 over the 3 rows.
The worksheet layout is as follows:
Date
A
B
C
D
Date
Rank
Header
[code]....
The return value under Header should be C.
I have reversed the order of the dates and put sequential numbers in B2:B4 as plug variables and the above formula will return the correct Header value but I need the formula to work with the current date order and repeating rank values of 1 to 4 in B2:B4. Do not wish to use VBA.
I'm struggling with match, index and lookup functions. I have a formula in column D that calculates the percentage of answers against the total score for that category. The way it calculates enables me to delete rows if they are not needed and still provide a total count and a percentage score.
Now the percentage is returned from a dynamic data set, I want to plot the percentages on a spider/radar chart. Instead of manually creating the chart or using a pivot, I wanted to lookup the percentages (column D) against the category (Column A) in question and plot so the chart updates as the user changes the answers.
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?
I'm trying to create a formula in cell f13 of my attached spreadsheet "Sample 1" that will search the 2nd attached spreadsheet "Sample 2" and return the correct serial number based on both the matching PO # (located in cell E10 on Sample Sheet 1 and in Column 5 on Sample Sheet 2) and Product # (cell A13 on my Sample Sheet 1). My current formula is not returning the correct result and I'm not sure why.
View 4 Replies View RelatedI have encountered a situation where I need to essentially accomplish a reverse Vlookup (using index match) and return multiple values.
View 1 Replies View RelatedI'm using this formula (thanks to dafan & HalfAce [url]
=IF(VLOOKUP($A177,$A$736:$C$787,3,FALSE)=0,"",VLOOKUP($A177,$A$736:$I$787,3,FALSE))
to return the value located in the 3rd column of the range when the value in the 1st column of the range matches the data in A177.
However, there isn't a match for the data in A178 in the range, so the formula returns "#N/A". I would like the cell containing the formula to remain blank in those instances.
I 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.
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
A
B
C
1
Cat 1
January 1, 2014
John
[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
{(INDEX($A$1:$C$4,MATCH(1,($B$1:$B$4>=B7)*(A$1:$A$4=A7),0),3))}
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.
I have a number of identicle tables, that have all been seperatly named. On a summary tab i am using an index match formula to pull back key pieces of data from the table that is identified by cell B2. So if in B2 their is SCB, i would like the formula to apply to the table SCB. The formula (when useing the word SCB works fine):
View 2 Replies View RelatedI am trying to use following string to find result into column F and unable to find reason for Error.
=INDEX($C$2:$C$5,MATCH(D2&E2,$A$2:$A$5&$B$2:$B$5,0))
A
B
C
D
E
F
1
part
code
price
find part
find code
Result
2
x
11
5
y
12
#VALUE!
[Code] ........