Array Formula not working for second criteria

vhdhfox

New Member
Joined
Aug 6, 2020
Messages
29
Office Version
  1. 2013
Platform
  1. Windows
Hi,

im using the below array formula to bring all data from column B with date as the criteria but when I use the same code for a second date on the same sheet no data is visible.

=IFERROR(INDEX(Sheet3!$B$3:$B$100,SMALL(IF(Sheet2!$A$1=Sheet3!$A$3:$A$100, ROW(Sheet3!$B$3:$B$100) -2,""),ROW()-2)),"")

the worksheet is setup so there is a merged date row and then a column header row below that then the data which is around 9 rows from the headers row.
there is a second date merged row below for the next date which is the one im having issues.
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,979
Office Version
  1. 365
Platform
  1. Windows
Without seeing the actual layout, I would say that you're asking the impossible. Please use XL2BB to post a capture of the relevant part of sheet3 (see link below), remember to fictionalise any confidential information first.

 

vhdhfox

New Member
Joined
Aug 6, 2020
Messages
29
Office Version
  1. 2013
Platform
  1. Windows
Sorry I cant use that the below are screenshots of the 2 sheets and their headers.


(Sheet3)
this she is where the data is pulled from
1599734477762.png

(Sheet2)
This is the sheet with the array using the date highlighted in pink as the criteria
1599734336756.png
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,979
Office Version
  1. 365
Platform
  1. Windows
From your first post I was under the impression that the merged cells were in sheet3 instead of sheet 2, looking at the screen captures I can see no reason for the formula not working.

Looking at the capture for sheet 2, I assume that you want to enter the formula into B3 then drag it down and right as far as M11, then repeat row B13 to M23 (possibly repeating the pattern with additional tables later)?

Doing a quick edit to the formula, is there a column in sheet 3 that only has text data (never a number)?
 

vhdhfox

New Member
Joined
Aug 6, 2020
Messages
29
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Yeah I'm a little confused as to why it isn't working as it works for A3:A11 but not A14:A23.

so at the moment I have the array in A3:A11/A14:A23 which will bring in unique numbers then use Index/Match formula using the cells with the array formula (A3:A11/A14:A23) as the criteria for the rest of the Index/Match formulas (B3:M11/B14:M23). there's probably a better way to do this but haven't found one just yet.

Columns G,L,M,N are the only columns that wont have any numbers in them.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,979
Office Version
  1. 365
Platform
  1. Windows
It might just be the row number at the end that was causing the issue (the one used for the k value of small), when you change the date criteria k needs to go back to 1.

A slightly different method, I haven't tested this as you were unable to post XL2BB captures (those we can copy and paste into a blank sheet).

This formula should do the same as your other one, but it doesn't need array confirmation. It should find the date in the merged cell automatically. Enter the formula into A3 then drag it down to A11, once you've done that copy A1:A11 as a range and paste to A14.

=IFERROR(INDEX(Sheet3!B:B,AGGREGATE(15,6,ROW(Sheet3!B$3:B$100)/(LOOKUP(1e+100,$F:$F,$A:$A)=Sheet3!$A$3:$A$100),ROWS(B$1:B3)-MATCH("ZZZ",$C$1:$C2))),"")

For the other columns, I would stick with index and match (if it works). As the columns in both sheets are in the same order, dragging the formula above to the right would work, but index and match will be more efficient.
 

vhdhfox

New Member
Joined
Aug 6, 2020
Messages
29
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

using this I get a 0 value in the field and an error message saying
1599738661658.png


1599738527386.png


are the highlight columns correct or should they be on sheet 3?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,979
Office Version
  1. 365
Platform
  1. Windows
Oops, the perils of an untested formula, looking at it again after seeing your post I see that I made more than 1 mistake with that part.

=IFERROR(INDEX(Sheet3!B:B,AGGREGATE(15,6,ROW(Sheet3!B$3:B$100)/(LOOKUP("ZZZ",$A$2:$A2,$A$1:$A1)=Sheet3!$A$3:$A$100),ROWS(B$1:B3)-MATCH("ZZZ",$C$1:$C2))),"")

All of the ranges are looking at the correct sheets, hopefully that is the only part of the formula where I made an error. I have some suspicions about the part in bold, which I think is going to be incorrect in the second part of the table if there are any blank rows in the first part.

If you're only going to be using it for 2 or 3 dates then I would suggest going with a simpler version and editing it as below.

A3 =IFERROR(INDEX(Sheet3!B:B,AGGREGATE(15,6,ROW(Sheet3!B$3:B$100)/($A$1=Sheet3!$A$3:$A$100),ROWS(A$3:A3))),"")

A14 =IFERROR(INDEX(Sheet3!B:B,AGGREGATE(15,6,ROW(Sheet3!B$3:B$100)/($A$12=Sheet3!$A$3:$A$100),ROWS(A$14:A14))),"")

If you're going to need it for a lot more dates then you can either continue editing based on the 2 examples, or I'll have another look at correcting the first method (if it fails again). I'll wait for your feedback before doing that but it will most likely be tomorrow evening (uk time) before I get chance to have another look.
 

vhdhfox

New Member
Joined
Aug 6, 2020
Messages
29
Office Version
  1. 2013
Platform
  1. Windows
Hi Jason,

Sorry for the late reply (Australian time) I didn't manage to get the first formula to work it was only showing the same cell value as the first row (A3) so I tried the other 2 and have adjusted for 5 dates which works perfectly for what I need, thank you for your help I appreciate it.

just another question the form is based heavily around userforms and some cells require hyperlinks to certain folders would it be possible to have a VBA script set up to use the cell value as a reference to find the correct folder and hyperlink that folder to the same cell used for the reference?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,979
Office Version
  1. 365
Platform
  1. Windows
would it be possible to have a VBA script set up to use the cell value as a reference to find the correct folder and hyperlink that folder to the same cell used for the reference?
It should be possible, as it is a different question it would be better to start a new thread for that.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,271
Messages
5,600,646
Members
414,398
Latest member
dhune

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?

Disable AdBlock

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
Top