array function

I am having 2 sheets in excel and I am trying to combine the data such that for every record in Sheet1, I get records from Sheet2 based on the common key. Below is the sample data to clarify: I have SHEET 1 and SHEET 2 Data and I want to generate something like SHEET 3. Can you please suggest...

I love the power of array formulas in spreadsheets, however my question is: Can I utilise this same power in a pure VBA User-defined Function? I have had a good look but can't seem to find how to do it? In my current use case, I want to do an INDEX/MATCH multi-value lookup. This screen shot...

I have data (on a table aptly named 'Data') for region, department, sub-department and profit/loss. I've made a drop-down list in cells A1, A2, A3, which filter respectively for region, department, and sub-department. Each drop-down list includes an option for no filter - an "All" option. I'd...

This seems to be an Excel 2013 bug ... <tbody> A B 1 something {=MATCH(FALSE, ISBLANK(A1:A1), 0)} 2 </tbody> (Use Ctrl-Shift ENTER to enter the B1 formula after typing without the curly braces) B1 should generate 1 whereas it returns #N/A. Change the range to A1:A2 and it works...

Hi All, My question is as follows: I am trying to match all the values in Column A of Worksheet 2 with values in Worksheet 1 that may be in Columns A, N, P, or X I want my formula to return the cell adjacent the matching cell so I have: {OFFSET(INDEX(NamedRange, MIN(IF(NamedRange = A2...

Hi Guys. This is my first forum post, so please bear with me if I ask a bit of a dumb question. I have a dataset that consists of nine headings: (1) Programme; (2) Tracking Area; (3) Indicator; (4 - 9) Countries (e.g. South Africa, France, Columbia, etc.). The dataset is populated with...

I can use "Evaluate" method to write the formula's result into a worksheet cell for a function like: ActiveSheet.Range("C27") = Evaluate("MAX('Test Data'!K:K)") which works fine and I get the value in the cell "C27" but when I try to use it for a formula like this...

I am having trouble moving data from one column (I am using this column as a helper column for data based on another sheet) into a different column on the same sheet, but without bringing along blank cells. This is the situation: <!--StartFragment--> <colgroup><col width="65" span="2"...

I have a really great formula (not my own design) which does 99% of what I want but falls at the last hurdle. My spreadsheet is basically a 2 column list of things, for example fruits, with a corresponding attribute, such as colours. So A:A might contain apple, apple, banana, banana... and the...

Hello, I am trying to copy about 100 formulas from a row, and then paste them 2 rows below where I copied, and then have it filldown til the last row.The issue is that the 100 formulas are not in the immediate columns, it can go A4 - F4, and then M4-N4, P4-T4... and so on. Here is what I have...

I have XML data delivered to cell B3 from a web service. When I extract the data using FILTERXML the cells in the array that go beyond the length of data delivered in the XML display #N/A. If I encase the FILTERXML in an IFNA or 'IF(ISNA...' it still displays #N/A. I am trying to not use VBA in...

Hi All, I'm new here, so sorry for any newb mistakes... I'm trying to find the max between sumifs of multiple adjacent columns in a range, meaning something like this: =max(sumifs(A1:A5, criteriaRange1, criteria1...), sumifs(B1:B5, criteriaRange1, criteria1...), sumifs(C1:C5, criteriaRange1...

My data is laid out in a large grid (194R X 50C) E6:BB6 is top row and E199:BB199 is bottom row. I want to check if some element is in each row of the grid and create an arrray of 1s and 0s. This is to multiply with another array of 1s and 0s that I already have so I can sum up the result...

Hi, i am trying use Indirect function in an array formula. ="{="&INDIRECT("J1")&"(IF((Sheet 1'!$B$2:$B$3761=$A9)*('Sheet 1'!$H$2:$H$3761=1)*('Sheet 1'!$J$2:$J$3761=1),'Sheet 1'!M$2:M$3761))}" Where the cell J1 contains a cell dropdown with options such as product, average, min, max and the user...

Hi, I am trying to create an array formula that does the following: Counts the number of times "Expression of wish" appears in 1 column and then "p" or "f" appears in another. So if I get Expression of wish appearing and P it will count it If in another row Expression of wish appears and F...

Two questions: 1) How can I pass two arrays into a user defined function? 2) How can I access single members of an array within a function? I'm trying to write a function that will automate my data-work up. I want to pass two arrays to this function, a set of x values, and a set of y...

After a day and a half of digging, I haven't quite found the answer I am looking for here. I have a large dump of monthly data related to properties which is monthly for 10 years. About 13k rows of accounts and 120 months I want to quickly sum the months in each year, and since I need to use...

I have build an excel file with VBA code that looks at the below matrix (which can adjust both on the x and y axis (larger or smaller matrix) and will hlookup the first "x" in a particular row and returns the associated Company (C1, C2, C3 etc). So I end up with another matrix this size with a...