My formula is: = INDEX (Lastsales,$022,$S$5)
O22 is blank
S5 =1
I am not getting an error message. I am getting data that is in Lastsales in column 1, row 19. What is Excel using for the row since $O22 was a blank?
Another interesting dilemma to solve. Using this formula:
View 2 Replies View Relatedlet 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.
I would like to look at an array and find the largest number. Once that is found, return the text value that is in column B from that row. For example:
Column....A.......B.........C.....D
....................Apple......5.....1
...................Banana.....2.....3
...................Orange.....2.....7
In this example, I would be looking at the array C1:D3. Because the highest value is 7, I want the text "Orange" to be displayed in a new cell such as A5.
To find the largest number, I am using =Large(C1:D3,1). How do I display its' corresponding text value from column B in a new cell?
How do I add a space between these 2 =Index functions
=INDEX(Customer!G$1:G$5,MATCH($C$35,Customer!$A$1:$A$5,0))&INDEX(Customer!H$1:H$5,MATCH($C$35,Customer!$A$1:$A$5,0))
As its displaying an address like this currently
22Newtonroad and I want 22 Newton Road
I tried with the VLookup, and HLookup and neither one worked for me. I have a reference table with all the data, and then input sheet where I want to bring this data by using one of the excel function. ie. When I enter office name on the top of the input page, I would like the Tax ID # in cell C3 to fill in using the excel function from the data on the reference tab. Same with cell C14 & C15. see attached for an example.
View 5 Replies View RelatedI've been working on a payroll program for my small biz. I'm close to done BUT...When I use the Index Match function in a cell it works great. However I need to copy it down a column to work for pay periods going forward. When I copy it down of course it puts zeros or N/A.
Columns:
Gross Fed FICA Medicare State Net
F G H I J K
Gross is user input. FICA Medicare State and Net are simply calculated on the sheet. But to get Fed Income tax I use the Index Match function and it works perfectly. The rows increment properly with each new user input.
My question: Fed Income tax is dependent on what the Gross is in order to look up the tables with the Index Match function. How do I get the result of the function into column G? Can I increment up Column G and ignore the zeros that dragging the function down G creates?
I am trying to sum values on sheet1 in column CR using the index function, but it comes up as #ref!
see formula below
=INDEX(Sheet1!1593:1609,96)
I have attached a sheet with what I am trying to accomplish...
I have several records that I am doing a lookup through. Most records have the same "work order number". As you can see on the attached sheet, my lookup function returns all the work order numbers that match my criteria.
What I need is to only return the first instance of the work order number then return the next unique work order number that meets my criteria.
Attachment 297239!
I would like to have an Index & match function in my userform.
I know that i must use it like this Application.WorksheetFunction but i am not able to do it.
User select a code and then in next text boxes, description and price should appear.
I am working on a spreadsheet which contains a number of reference data sheets (named “Reference data 2009”, “Reference data 2010” etc). As their names suggest, these sheets contain reference data applicable to the particular year. This reference data is used to perform various calculations in a “Calculations” sheet.
On the “Calculations” sheet, the user specifies the year for which they wish to perform calculations. At present, I am using the volatile INDIRECT function to perform various HLOOKUP calculations along the following lines:
=IF(D15>=HLOOKUP('Detailed net pay calculations'!D16, INDIRECT("'Reference data" & 'Detailed method'!$C$2 & 'Detailed net pay calculations'!D1 & "'!$B$43:$Y$52"), 4, FALSE), “Do something”, “Do something else”)
The reason I'm using the INDIRECT function is to identify the sheet with the appropriate year (hence "Reference data"&'Detailed method'!$C$2&'Detailed net pay calculations'!D1 which could be interpreted by Excel as "'Reference data 2009NEW" or "'Reference data 2010", depending on whether there's text in cell D1).
My query
The function I'm using is working perfectly fine but I am wondering if it is possible to replace the INDIRECT function (in red) with INDEX or another non-volatile function in order to reduce the performance impact (I have a fairly large number of these types of functions).
I'm looking for the reason why the following use of the INDEX function returns a cell address [ie the formula evaluates SUM($C$2:C3)]-
View 5 Replies View RelatedPlease see the attached worksheet for details. I would like the array function to search for instances of the word "FALSE" in column E and return the values of columns A:D when a match is found. I have done this successfully when the lookup value is a value in the first column of the range, but cannot seem to do so when the lookup value is in the last column of the range. I have received a #NUM! error each time.
View 9 Replies View RelatedIs there a way to use the INDEX function to return Blanks or NAs instead of zeros when the corresponding row & column match is a blank/null cell?
e.g. =INDEX(F10:H13,1,1) returns 0 even if cell F10 is blank?
I have different worksheets to contains information from different years. To make it comphrensve, I have created a mastersheet which contains all the info across the worksheets. And once I make an entry in the worksheet, I hope the mastersheet can be updated automatically. the function I set is
INDEX('2011-12'!K$4:K$7,MATCH(Master!$B5,'2011-12'!$B$4:$B$7,0),1).
Yet, my problem is..... once I perform sorting in the mastersheet, the cells are wrongly matched. So, I just wonder how I can revise the function?
I compile ratings for racehorses and am currently extracting the data I need using an array formula. Unfortunately there are are many thousands of lines of data and it can take quite some time for the data to pull through.
Here is a sample of the formula's I am currently using. I cannot take credit for the formulas by the way I found them on the web.
IF(ISERROR(INDEX('Database 2012'!$A$1:$B$300,SMALL(IF('Database 2012'!$A$1:$A$300=Ratings!$A3,ROW('Database 2012'!$A$1:$A$300)),1),2)),"",INDEX('Database 2012'!$A$1:$B$300,SMALL(IF('Database 2012'!$A$1:$A$300=Ratings!$A3,ROW('Database 2012'!$A$1:$A$300)),1),2))
Basically I have 2 sheets (Database 2012, Database 2011) with the horse names with ratings next to them for each run, One sheet for this season one for last season.
These are sorted in date order. then I have a ratings sheet that shows the horse name and then to the right it has columns for each of its last 4 ratings.
The formulas I use look for the horse name on the database sheet then put the rating in latest rating in the first column then the 2nd latest in the next column and so on until the last 4 ratings are shown. If there are not 4 ratings to find the is an iserror at the beginning of the formula to populate that cell with a blank so that the errors dont show.
What I want to know is there a way of doing what the formula does using VBA and more importantly would it be quicker than using array formulas which are very slow to recalculate when there are 1000's of lines.
I am trying to use index & match function to extract data from data base with has multiple critera in rows as well as cloumns but it is not giving correct results.
Example:
A
B
C
D
E
[Code].....
In C9 I typed =INDEX($B$2:$E$6,MATCH($A9,$B$1:$E$1,0),MATCH($B9,$A$2:$A$6,0))
The result I got was #Ref!
what formula should I use in C9 to get 20, C10 to get 5 & so on
FB1 to FB4 are column headers (critera)
GL 1 to GL5 are row headers (critera)
So here is how I have a template laid out:
A
B
C
D
E
F
ROW 1
Period 10
Period 11
[Code] ........
Cell E4: =SUMIF('P10-2011'!$A$4:$A$272,'Budget Upload'!$A19,'P10-2011'!$H$4:$H$272)
I have a SumIF statement in cell E4 but I would much prefer some sort of index or something where I can drag the formula down because the cells in column D will change (and require me to pick a different SUM range for my SUMIF function). Now my issue is I have multiple matches so when I use a typical index it returns the FIRST match.
Here is the formula I used in cell E11 to try and replicate the result in E5:
=INDEX('P10-2011'!$A$2:$Y$272,MATCH('Budget Upload'!$A4,'P10-2011'!$A$2:$A$272,0),MATCH('Budget Upload'!$D4,'P10-2011'!$A$2:$Y$2,0))
My index function works as it is designed but only returns the FIRST match. I should have multiple matches.
Is there a way to use an index function to return multiple results?
To clarify,the index function I put in cell F4 returns only ONE column (which is correct and will always be correct), which is related to the cell D4 but I have multiple rows (related to "7002" row match portion of the index function).
I've been working on a payroll program for my small biz. I'm close to done BUT...
When I use the Index Match function in a cell it works great. However I need to copy it down a column to work for pay periods going forward. When I copy it down of course it puts zeros or N/A.
Columns:
Gross Fed FICA Medicare State Net
F G H I J K
Gross is user input. FICA Medicare State and Net are simply calculated on the sheet. But to get Fed Income tax I use the Index Match function and it works perfectly. The rows increment properly with each new user input.
Code:
=INDEX(SingleWH!C$6:M$140,MATCH(G7,SingleWH!A$6:A$140,1),MATCH(E$1,SingleWH!C$4:M$4,0))
This is the code in Column F. You can see where it uses the result in Column G to look for a value in sheets("SingleWH").
My question: Fed Income tax is dependent on what the Gross is in order to look up the tables with the Index Match function. How do I get the result of the function into column G? Can I increment up Column G and ignore the zeros that dragging the function down G creates?
I have two worksheets, named as follows:
Worksheet1: SUMMARY
Worksheet2: ART 2013
'ART 2013' contains a list of staff numbers in column C16:C752 and a list of dates in column M16:M752 (formatted as "mm-yyyy")
Within 'SUMMARY' cell G12 contains the date 04-2013.
What I would like to do is achieve a function that will look at 'ART 2013' columns C and M in and where the date in Column M is equal to cell G12 (04-2013) of the 'SUMMARY' worksheet excel will return the Staff Number in 'ART 2013' column C.
I'm trying to replicate the iferror index match function from excel into vba. The excel version is
=IFERROR(INDEX('billing-data'!B:C,MATCH(C2,'billing-data'!C:C,0)+0,1),"")
Which works fine. In vba I have:
Cells(i, 19) = Application.WorksheetFunction. _
Index(Sheets("billing-data").Range("B:B"), _
Application.WorksheetFunction.Match(Sheets("payment-data").Range("c" & i), _
Sheets("billing-data").Range("C:C"), 0), 1)
There are some mismatches and I get an error message with this which prevents the all the data from being displayed.
I'd like to show Cells(i, 19) as null or blank if there is a mismatch or error.
I'm trying to create an index array that resizes its array starting location.
{=IFERROR(INDEX($V$17:$AU$17,1,MATCH(TRUE,V18:AU18
I have a folder with about a thousand files in it, each one holds one worksheet.
What i need is a new file which lists all these file names in column A and, in column b, the value from cell A1 in each file against the file name, i.e.
book1 Apple
book2 Banana
I can list the files in the folder using dos, but cant rip the a1 cell values out,
so I need to open each workbook in turn, record the file name, and then record the value in cell A1 against it.
I have a table A3:C118 and I am filtering the data and finding the subtotal for the maximum value in column C. How do I find the corresponding data point for column A? I have tried several formulas and they are not giving me exactly what I need.
View 3 Replies View RelatedHow do i use the Index Function for Filtered Cells. The code below will ignore the hidden or filtered cells. =INDEX($A$3:$A$100,B1)
View 6 Replies View RelatedI am trying to get the cell index from a cell reference passed to a function and I have no idea how. eg. In excel
=UDF(D4,...,...,)
What I want is to get is the row and column index for cell D4, not whatever is in cell D4, so I can just click the cell in excel, and drag the formula about etc.
I'm currently using this formula:
=INDEX(B4:B64,MATCH(I5,A4:A64,0))
If I5's value is displayed more than 1 time in Column A, I want the information in Column B to appear in the same cell the formula is in in Column J
I am attempting to get an INDEX function with multiple "IF" statements to return more than one result. Building multiple IF statements. That portion of the below formula works. However, I can only get the INDEX formula to return the first, single result.
Formula:
=IFERROR(INDEX(Func_Area,SMALL(IF(Const_Start<=DATE(YEAR($B$2),MONTH($B$2),DAY($B$2)), IF(Cost_End>=DATE(YEAR($C$2),MONTH($C$2),DAY($C$2)),ROW(Func_Area)-MIN(ROW(Func_Area))+1,"")),ROWS($A$4:A4)))," ")
Func_Area,Const_Start,Cost_End are all Named Ranges on Sheet "Proposed". My intention is to return a list of projects on Sheet Test1 for all projects starting construction after a certain date and completing construction before a certain date. I suspect something in my ROWS fuction or ROW-MIN+1 is wrong.
This formula returns the first result in the first row, but every subsequent row is blank...see attached spreadsheet.
I have attached a workbook.
I have a calculated value in H2. This value will correspond to values in the table provided. This value is dynamic and will change based on criteria that will be added.
The value H2 is a combination of to "letters" D1 and A2 or A4 and B1 i.e. trace back where the intersection of H2 occurs within the table
In Cells A8 and A9 I am trying to represent which combination of letters will result in an intersection of the value H2. In the example provided it is C and A.
What i would like to do is have Cells A8 and A9 be more dynamic. So if for example the value in H2 changed to say R12,06 then the intersection would be B and B. Therefore Cell A8 = B and Cell A9 = B
The formula is cell A8 and A9 doesn't cater for this as the range is fixed. I need a dynamic range for the index and match function based on the where the intersection of H2 happens to be in the table.
I'm matching ISO country codes to country names.
This works great as long as the 'lookup_value' portion of the match fuction matches the 3 digit code on Sheet3.
Is it possible to return my desired result without this matching perfectly.
So a value in 'Sheet1 Column A' that begins A344xxxxxxx will return the same result in 'Sheet1 Column F' as if 'Column A' just had a 3 digit number.