Array formula: skipping a row that returns a "FALSE" result

Question

Hello,

   I am working with a large data set and need to find a way to automatically import numbers from one sheet to another based on certain criteria.

   Sheet1 has the raw data which is automatically generated once a month. Every instance is listed on a separate row.

   Sheet2 uses a SUM(INDEX-MATCH) formula to create a pivot table. This groups all instances by a unique identifier as well as sums multiple instances within the same calendar month.

   Here is the formula: =IFERROR(SUM(INDEX(Sheet1!$E$1:$E$1011,MATCH(TRUE,EXACT($A2&B$1,Sheet1!$A:$A),0)),IFERROR(VLOOKUP($A2&B$1,OFFSET(Sheet1!$A$1,MATCH($A2&B$1,Sheet1!$A:$A,0),0,1000,5),5,0),0),IFERROR(VLOOKUP($A2&B$1,OFFSET(Sheet1!$A$2,MATCH($A2&B$1,Sheet1!$A:$A,0),0,1000,5),5,0),0),IFERROR(VLOOKUP($A2&B$1,OFFSET(Sheet1!$A$3,MATCH($A2&B$1,Sheet1!$A:$A,0),0,1000,5),5,0),0),IFERROR(VLOOKUP($A2&B$1,OFFSET(Sheet1!$A$4,MATCH($A2&B$1,Sheet1!$A:$A,0),0,1000,5),5,0),0),IFERROR(VLOOKUP($A2&B$1,OFFSET(Sheet1!$A$5,MATCH($A2&B$1,Sheet1!$A:$A,0),0,1000,5),5,0),0)),"")

   I had to repeat the formula to catch up to 6 instances within one month. The YTD columns simply add all months up to that point together.

   The below table is the result of the formula.

	A	B	C	D	E	F
1	Unique Identifier	January	February	YTD1	March	YTD2
2	22332	260.75	229.99	490.75	251.67	742.42
3	25760	67.15	60.65	127.79	67.15	194.94
4	26050	100.00	100.00	200.00	100.00	300.00
5	27445				71.90	71.90
6	30614	62.50	50.00	112.50	75.00	187.50

   Sheet3 is where I am having the problem. I need this sheet to look at Sheet2 and pull the unique identifier of all those that meet a cumulative amount greater than 100 and less than 750. This array formula currently works:

{=IFERROR(INDEX(Sheet2!$A2:$X2,SMALL(IF((100<=Sheet2!$B2:$X2),MATCH(ROW(Sheet2!$A2:$X2),ROW(Sheet2!$A2:$X2)),""),ROWS(Sheet2!$A2)),COLUMNS(Sheet2!$A2)),"")}

   However, it is not perfect as it will return a blank cell if the corresponding row that it is looking at does not meet the criteria (less than 100). See table below:

	A
1	Unique Identifier
2	22332
3	25760
4	26050
5
6	30614

   The problem is with cell A5, because it is blank. I need column A to have no blank cells, until it reaches the end of the data in Sheet2, like this table:

	A
1	Unique Identifier
2	22332
3	25760
4	26050
5	30614
6	... continue down the list of data

   I am looking for a formula that will look at Sheet2!B2:X2, if it finds a value greater than 100, it should return A2 (unique identifier). If it searches B2:X2 and finds only values less than 100 (see row 5 as an example), then it should move to B3:X3 and search there WITHOUT RETURNING A BLANK CELL FOR A2. This is the part that I cannot overcome.

   I have tried several variations of INDEX-SMALL-MATCH, INDEX-MATCH and INDEX-SMALL formulas without success.

   Any help would be most appreciated!

Answer 1

Delete entries in all cells in the range B2:F6 and enter again, enter the array formula again in cell A11 and copy down. If this does not work do below.

You mention that my file is working fine. Please copy / paste columns A to F from my sheet to your file and check. If this does not work then please upload your file to enable a resolution.

Regards

Answer 2

A compact formula:

Enter below formula as an array formula (Ctrl+Shift+Enter) in cell A11 and copy down:

=IFERROR(INDEX($A$2:$A$6,SMALL(IF(MMULT(1*($B$2:$F$6>=100),TRANSPOSE(COLUMN($B$2:$F$6))^0)>0,ROW($B$2:$F$6)-ROW($B$2)+1),ROW(1:1))),"")

Regards,

Amit Tandon

www.globaliconnect.com

Answer 3

Hi,

This is the array formula I have written in cell A11 and copied down

=IFERROR(INDEX(sheet2!$A$2:$A$6,SMALL(IF(INDEX((MMULT(1*($B$2:$F$6>=100),ROW(INDIRECT("1:"&COUNTA($B$1:$F$1)))^0)>0)*(ROW($A$2:$A$6)-ROW($A$1)),,)=0,10000,INDEX((MMULT(1*($B$2:$F$6>=100),ROW(INDIRECT("1:"&COUNTA($B$1:$F$1)))^0)>0)*(ROW($A$2:$A$6)-ROW($A$1)),,)),ROW(1:1)),1),"")

Hope this helps.

Answer 4

Sorry about that. I tried pasting an image into the body of my post but that didn't work. I edited the post to show you the formulas and tables. Thanks!

Answer 5

Hi,

Could you share a dataset and also show the expected result.