index array

I'm using an index match formula where the index is a named table called "UnemploymentTable" on another worksheet within my Excel workbook (see screen shots below). The trouble I'm having is when I drag my formula to the right, the index/match formula is going to my table and looking up one...

Hi, I had formula: = INDEX($E$24:$AK$24,1,$E$8) How can I here add offset, that function will return value number from row 25 ? (now its value from row = 24, but I need value from row = 25).

Hey guys, A bit of a convoluted question. I have a list of values <tbody> for127 for128 for13 for1 for56 aga567 aga2 aga67 for12 </tbody> I want to be able to enter a substring, in this case "for" and for it to return the next available ID. So in this case the last one...

Hi Everyone, I am working on an Excel Workbook that has 14 tabs or worksheets. Worksheets are "Summary" "Year End 2014" and each month... ("Jan" "Feb" "Mar" etc...). At the end of each month I get a report with all of our sales that I copy and paste into the corresponding month's tab. On my...

I am attempting to pass historical price data for 5 stocks into a 3D array and then do calculations there (and keep in dimension 3). My array is called "matrix" in the workbook attached, and the 3 dimensions are: 1-number of periods (in this case, days), 2-number of stocks and 3-price data and...

I've found 30 to 40 similar examples on formulas that should solve my problem, but so far none have worked. One thing I have noticed with many of these examples is the requirement to press 'Control + Shift + Enter' for an array formula. I have Excel 2013 and no brackets appear and I'm not sure...

Hi all I have a large data table in varying size 300 - 6000 rows, 20 columns. I am trying to sort the data for display purposes, mainly graphs, so I am using.. Sheets("Data Display").Range("B33:B6033").SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Calc").Range("A10000") and...

I'm trying to search 2 different columns containing strings for keywords, which I have put in an array. I'm also searching a couple of other columns for some specific numbers. If conditions are met, I want to flag a cell with an "x", if conditions are not met, I want to mark it with an "-"...

Hey, I have a bit of a problem, I need a formula to look something up based on two variables. So the info that will be input is Name, Group and Age, eg. Name: John Group: Blue Age: 40 Member Price: I need it to draw the member price based on the group and age; <tbody> Group Age Range...

Hi, The formula below is a lookup using multiple values. It works well however I feel as if it could be neater. I am also bordering on the nesting limits if I would like to add any additional formulae. Could you please help me shorten it...

Help Required My problem is i want to past the complete cell of that particular teacher with (subject, name of that teacher & Class) in there receptive ROW, it will help me to know which subject & Teacher and which class that particular teacher is teaching in "Sheet 1" i have highlighted...

I very much hope someone can assist. 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 <tbody> A B C 1 Cat 1 January 1, 2014 John 2 Cat 2...

I am building a financial model that has one tab that has hard-coded data and another tab that is a formatted monthly financial statement. I am trying to formula that looks at the current month and the ID # and SUMS all of the data on the input tab that has that specific ID #. I know this is...

Hi, I am looking to create an index array. the conditions for the array are based on drop down menus. I want to have it that if the drop down menu has * in it, the array should calculate everything rather than a specific value. ={IF(ROWS(I$14:I14)>$H$7,""...

Hello Everyone, i'm using an INDEX(SMALL()) array formula to search for multiple rows in a list which match a cell's value. It works perfectly for exact values, but when i use wildcard characters i'm not having any luck, can someone please help. example below: The above works correctly if...

Hi, I hope someone can help me with the best solution for this. I have a tab that contains thousands of different records, both rows and columns. I want to create a summary sheet that only gives me certain columns and based on 2 lookup values. The user would change these 2 values to suit...

Image- This worked perfectly at first, so I thought. But for some reason it is pulling the letter 1 down from it's match (See highlighted matches and it returns that below it). The far right in red is a text only sample of desired outcome from the data on the far left. I've tried so much. I...

Hi, I am hoping to use Data Validation to create a drop-down list from a dynamic column that contains a mix of text cells and number cells. To make it more complicated, the list needs basically every convenience feature known to man (or that I can think of): it needs to be dynamic...

Hi all, I've been trying to build a dynamic array that can be changed by using 2 combo boxes. I have managed to populate an array of "pizza places" that can carry the same peer group. So that one dropdown box selects a pizza place the other selects a peer group. Currently, if I select "Pizza...

Need to do horizontal sum, (see example data block) <colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody> Month1 Month1 Month1 Month1 Month2 Month2 Month2 Month2 Week 1 Week 2 Week 3 Week 4 Week 1 Week 2 Week 3...