# INDEX-MATCH with IF and logical AND

#### hurtlingturtle

##### New Member
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

cheers
Bruce

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Welcome to Mr Excel forum

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

M.

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.

good plan, thanks

Replies
2
Views
94
Replies
3
Views
86
Replies
3
Views
209
Replies
1
Views
178
Replies
1
Views
374

1,203,240
Messages
6,054,316
Members
444,717
Latest member
melindanegron

### 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.

### Which adblocker are you using?

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

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