Index Match function with specific criteria

tmckivergan

Hello,

I have successfully put together a formula to pull an Investigation tracking number to a cell when the product name on the new sheet matches the product name on the Investigation Tracker sheet.

=IFERROR(INDEX({Investigation Tracker Range 1}, MATCH([PO Item Description]@row, {Investigation Tracker Range 3}, 0)), "")

What I would now like to do is to only have this pull the investigation number when the Investigation Tracker status column is anything but "closed".

Any advise is appreciated.

Tom

DKazatsky2

Hi @tmckivergan,

Give this a try.

=IFERROR(INDEX(COLLECT({Investigation Tracker Range 1}, {Investigation Tracker Range 3}, =[PO Item Description]@row, {Investigation Tracker Range 4}, <>"closed"), 1), "")

{Investigation Tracker Range 4} is a cross-sheet reference to the status column.

Hope this helps,

Dave

tmckivergan

That worked perfectly. Thank you very much