Help! Formula to return value if "Open" (Vlookup / Offset?)

Hey there, first posting! I'm hoping someone might be able to help me out.

excelexample.jpg

As an example, I'd like a formula to look up each status of 43842, and if it's open, return "Open" and "1". If it's anything but "Open", it can return cancelled or open, doesn't matter.

I used this formula initially. It works but then completely bogged down my computer (array and it's for a huge file).

{=INDEX(Pivot!$A:$C,SMALL(IF(OFFSET(Pivot!$A:$C,0,0,ROWS(Pivot!$A:$C),1)=A6,
ROW(OFFSET(Pivot!$A:$C,0,0,ROWS(Pivot!$A:$C),1))-ROW( OFFSET(Pivot!$A:$C,0,0,1,1) )+1,
ROW(OFFSET(Pivot!$A:$C,ROWS(Pivot!$A:$C)-1,0,1,1))+1),COUNTIF(OFFSET(Pivot!$A:$C,0,0,ROWS(Pivot!$A:$C),1),A6)),2)}

This formula returned the status of the last instance in the data, so worked kind of.

Any help is appreciated!!

Hello mgrunb and Welcome to Excel Forum.
It would seem that you are only a formula to return the # as you are only looking for one 'Status' (i.e. Open).
Since you are using an Excel version newer than 2010 you could use the AGGREGATE function as in: =INDEX(C$2:C$5,AGGREGATE(15,6,(ROW(A$2:A$5)-ROW(A$1))/(A$2:A$5=E2)/(B$2:B$5=F2),1))
If this doesn't work for your purposes please upload a sample data file, not a screenshot, with the desired results manually displayed so that we can attempt to write formulas/code to replicate them.
To upload a sample workbook click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.

Is it are you looking for:

F1=LOOKUP("ZZZ",$B$1:$B$10)
G1=VLOOKUP(F1,B1:C10,2,0)

Untitled.png