INDEX-MATCH with IF and logical AND

[hurtlingturtle]

Hi All,
I hope someone here can help me out. I've been searching everywhere and cannot find what I need.

To explain, I am working on a workbook that has a table within it that contains load of rows of data. This table is called TeamSheetsAll. Within that table are many columns, but relevant here are the columns [Fiscal period] and [over 1m]. Fiscal period is a text value whereas over 1m is a boolean TRUE/FALSE value. The other field that is important is the [Op ID] which is a text value to be returned.
I should also state that the A6 references are used to check an increasing list of data in my new table to ensure I do not duplicate rows.

I am trying to generate a unique list of op ids that meet the specific logical requirements of my IF statement. The formula I currently have is as follows. But this results in empty data.

=IFERROR(INDEX(IF(AND(TeamSheetsAll[Fiscal period]=CurrentFQ,TeamSheetsAll[over 1m]=TRUE),TeamSheetsAll[Op ID],""), MATCH(0,COUNTIF($A$6:A6, IF(AND(TeamSheetsAll[Fiscal period]=CurrentFQ,TeamSheetsAll[over 1m]=TRUE),TeamSheetsAll[Op ID],"")), 0)),"")

However, if I do this without the AND logic then the following formula works.

=IFERROR(INDEX(IF(TeamSheetsAll[over 1m]=TRUE,TeamSheetsAll[Cloud Op ID],""), MATCH(0,COUNTIF($A$6:A6, IF(TeamSheetsAll[over 1m]=TRUE,TeamSheetsAll[Cloud Op ID],"")), 0)),"")

Similarly if I replace this with OR logic it works also. AND does not. Just to be clear I have triple checked the data and I absolutely do have data records in the table that meet the AND criteria

Any advice is most welcome.
cheers
Bruce

 

[Marcelo Branco]

Welcome to Mr Excel forum

A small data sample (~10 rows) along with expected results would be helpful.

M.

 

[Marcelo Branco]

To post a data sample: see Section B in
https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

M.

 

[Sense-A]

If I were you, I would just use a helper column in the data set. For example, you want TeamSheetsAll[Fiscal period]=CurrentFQ and TeamSheetsAll[over 1m]=TRUE, so create a helper column to get CurrentFQTRUE and then index and match for that combined term.

Otherwise, a sumproduct might be in order.

 

[hurtlingturtle]

good plan, thanks