# array formula

1. ### Array formula with column reference making macro too slow

Hi all, I'm new to using array formulas so I am struggling to work out how to speed up an array formula. My array formula returns the latest referral date against each customer (column A stores the unique customer ID and column U stores referral dates): {=MAX(IF('ACP Data'!A:A=\$A2,'ACP...
2. ### Find nth Unique Value

I'm looking for a formula that can do one of two things. Ultimately I don't care which one it does because I can make either of them work but if I had my preference I would prefer something like Solution 1. I have two worksheets (let's call them SheetA and SheetB) each with the same headers...
3. ### simple array formula

Hello everyone, Hopefully you can help me with this. So i have a list of values (not in any cell) that I need to check against a single value. I want to check if 2 exist in {3,4,6,12,2} and the result would be TRUE or FALSE My intial statement was =2={3,4,6,12,2} but the results comes in...
4. ### POSITION of nth smallest value in multiple criteria

I am getting nth smallest value with multiple criteria but couldnt get the position of it. I need postition to lookup other values in the same row of the nth smallest I used the following formulas =IF(AK\$2=0,"",IFERROR(SMALL(IF(\$K\$4:\$K\$20=AK\$2,\$I\$4:\$I\$20),\$U\$4:\$U\$20),"")) No issue with this...
5. ### Help with array formula

Looking for advice on array formulas. I'm a school teacher making a spreadsheet for my school that plots children's progress in different subjects across the year. Someone helped me write this formula which I have been able to apply to different terms in the same subject (columns A:N in one...
6. ### COUNTIFS ARRAY VALUES

I am trying to build a formula where the below values are pulled from a table where......SUPPORT = A1 WINTEL = A2 CITRIX = A3. The only way I can get the formula to work is to list out the value string as indicated below in purple. If I ever want to change the value string, I would like to...
7. ### Multiple criteria sumif array formula with 'only look at range above this row' logic

I’m trying to create a tab in a live google sheet that acts like a bank register with multiple accounts. It would have a sender ID column, a receiver ID column and a transaction amount column. What I’m now trying to build is a column which outputs an account balance for the sender at the point...
8. ### Quicker way to process more than one row using Index Match

Hi, I have the below base data that I want to split in to individual sheets. So that each ID has its own sheet. ID heading is in cell A1 ID Name Detail Date 1A Joe Arm procedure 24-Mar 2A David Leg procedure 12-Feb 3A Sarah Shoulder injection 01-Apr 1B Hannah Knee Pain 03-Feb 2B...
9. ### Find one possible match in a matrix (3 criteria, array formula)

Hi All, The situation that I have is the following: Sheet1: (start and end times of orders) Machine (CELL B1) OrderNumber (CELL E1) StartTime (CELL P1) EndTime (CELL Q1) Machine1 00001 Machine2 00002 Machine3 00003 Machine4 00004 Machine1 00005 Machine2 00006...
10. ### Array formula with lookup based on multiple criteria including a date range

I tried to find help with this question on Stack Overflow here but did not get a complete answer. I use this Google sheet to track proofreading jobs. I'm trying to modify the array formula in P1 that calculates the billable total for each job. The formula currently uses the Turnaround and...
11. ### COUNTIF in array formula isn't allowed, while COUNT is. Why ?

Can you please help me find a way around Can't figure out why this one working: {=COUNT(ISNUMBER(SEARCH(List,E522))*(ROW(List)-1))} Which evaluates in something like COUNT({2,4,6,2}). And it results in 4 Now I want to count only 2. So I use...
12. ### Trying to understand how SEARCH work

So I use the same database, the same cell, same formula. But somehow I get different behavior of formula when I evaluate it. Here I use SEARCH as a single formula {=SEARCH(List,E521)} And I get only first item from the List (table). But here I use SEARCH in combination of formula...
13. ### Changing an array formula to a regular formula

Hi all and thanks in advance... I have the following array formula which is working as intended =SUMPRODUCT(((CONTACTS!\$A\$2:\$A\$5000=A2))/COUNTIFS(CONTACTS!\$A\$2:\$A\$5000,CONTACTS!\$A\$2:\$A\$5000&"",CONTACTS!\$R\$2:\$R\$5000,CONTACTS!\$R\$2:\$R\$5000&"")) Problem is it takes way to long to calculate. Is...
14. ### VBA Array formula help

I have the below array formula in a spreadsheet that i am currently updating so that most of the data is stored in VBA Arrays rather than in cells. I'm having trouble with this array formula. Rather than having the source data in D4:LasTRow2,LCol2 it is now in a 2d array called SA_Corr which...
15. ### Array Formulas not necessary

I was asked to help my boyfriend on a vlookup with multiple conditions. I know that requires using Index Match and using them as an array formula. However when he went to hit "enter" he didn't want to use "ctrl, shift, enter" as I told him but it still ended up working. At work today I...
16. ### VLOOKUP Fails

I've built a timesheet with 15-minute increments. Users enter their task and what project the task is part of, and a single project can have multiple tasks. All projects are in one column and all tasks are in a neighboring column. Total time is listed beside them. The range is D2:G166 (NOTE...
17. ### Excel Formula - Incrementally Add a +1 to a Header Name each time it is copied/pasted

Hello Group, I am working on a project and need one last piece to wrap up this file. Explanation: I have a block of Data B2:N15, There are various formulas within the block that link to other sheets and some manual entry fields. I have set up these blocks so that a user can copy a block and...
18. ### Array formula alternative

I have a spreadsheet that I use to track time logs against projects, for my entire team. I track this on a weekly basis. Unfortunately, the tool we use doesn't give me the breakdown of change each week. It just gives me the total time spent against the project. Given this, each week I export...
19. ### Excel Office 365, to create an Array formula- "Ctrl-Shift-Enter" is not making the formula work

Hello. In Excel Office 365, to create an Array formula- "Ctrl-Shift-Enter" is not making the formula work rather it produces the error code #VALUE ! - here is the formula- thank you in advance for your help on this...
20. ### Conditional Formatting did not work with SumProduct and Search array formula

Hi, I have a table called BadSyntaxTbl with a column called Bad Syntax. It looks like this: Bad Syntax <!-- b-->< /b> % { I want to apply conditional formatting to a range of cells. The formatting would apply when any of the terms in Bad Syntax column are found anywhere in the formatted cell...

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back