Extract 2 corresponding fields by Date/Month/Year selection

Hi,
Would like to extract the Status Code and Policy name by Date from the raw data (2 worksheet). I have used in the index/match formula as below but it's not working. The data gets repeat.

Attached is the file for your preview. I am not sure where I missed it. Please advise.

In C12 copied down, this ARRAYED formula

=IFERROR(INDEX(INDIRECT("FailBk"&$AD$6&"[Policy Name]"), SMALL(IF(YEAR(INDIRECT("FailBk"&$AD$6&"[StartDate]"))=$AD$5, IF(TEXT(INDIRECT("FailBk"&$AD$6&"[StartDate]"),"mmmm")=$AD$4, ROW(INDIRECT("FailBk"&$AD$6&"[StartDate]")))),ROWS($A$1:$A1))),"")

I do not see a place in your table for "Status Code" but it would be identical with "Status Code" replacing "Policy Name"

Do you want to extract based on just what is AN9 (month and year) or a combination of AM9 and AN9 (exact date)?

Okay, coffee is helping

In AM12 copied down, this Array formula

=IFERROR(INDEX(INDIRECT("FailBk"&$AD$6&"[Status Code]"), SMALL(IF(INDIRECT("FailBk"&$AD$6&"[StartDate]")=DATEVALUE($AM$9&$AD$4&$AD$5), ROW(INDIRECT("FailBk"&$AD$6&"[StartDate]"))-MIN(ROW(INDIRECT("FailBk"&$AD$6&"[StartDate]")))+1),ROWS($A$1:$A1))),"")

Similar in AN12

I had neglected to take into account the data doesn't begin in Row 1

Hi,

Sorry, I am retesting the data and will let you know the outcome.

Hi,

The formula in the file attached in your post, it's working fine. However, when I copied the formula in a different file which I was working on, it's not working.
I still couldn't figure out why. Column C12 has to be a unique value as per user's request. Only Column AM and AN having issues.

Please refer to the attachment I am was trying to copy and I need to know why it's not working.


I am trying to understanding the formula but I need some clarification:

1) INDEX(INDIRECT("FailBk"&$AD$6&"[Status Code]") = To display the status code.
2) SMALL(IF(INDIRECT("FailBk"&$AD$6&"[StartDate]")=DATEVALUE($AM$10&$AD$4&$AD$5) = if 1/9/15 = selection (AD5&AD4&AM9). Why use SMALL function?
3) ROW(INDIRECT("FailBk"&$AD$6&"[StartDate]")) = When press F9 it shows row 461, 462....which i don't understand
4) -MIN(ROW(INDIRECT("FailBk"&$AD$6&"[StartDate]")))+1) = why minus 2+1?
5) ROWS($A$1:$A1))) = row increases.

Small function is to find the 1st or 2nd smallest number, correct?

Okay, starting with the first issue, the reason it does not work is simply that you have an error in TF3 StartDate (I681). That error carries through to your formula giving you an error which the IFERROR catches. Either fix that cell or fix the formula to catch errors (IFERROR(INT(...),"")

Next the explaination of the formula. The INDIRECTS just convert TEXT to Ranges of Cells so we'll ignore them. We'll also ignore the IFERROR, and we'll replace some of your cells with the constants. That makes the formula more readable.

=INDEX('FailBkTB3'![Status Code]), SMALL(IF('FailBkTB3'![StartDate]")=DATEVALUE("1September2015"), ROW('FailBkTB3'![StartDate]))- MIN(ROW('FailBkTB3'![StartDate])))+1),ROWS($A$1:$A1)))

The IF function creates a new array of row numbers that match 1 Sept 2015. Because your data doesn't begin on row 1, the formula subtracts the first row + 1 (MIN(ROW('FailBkTB3'![StartDate])))+1. For example if the first row that matches is row 6, it would be 6-2+1 or 5 (it is the 5th row of data).

The SMALL returns a single value for the INDEX to use to return a startdate.

ROWS($A$1:$A1))) is the second argument in SMALL that, increments as you drag down, 1,2,3...

Does that help?