Index-Match that pulls all values from an excel

[Teleporpoise]

Hello Mr. Excel Friends,

I have an excel sheet that keeps track of the status of some items, broken into OK or NOK status. I need a log of all NOK parts in another sheet, I have tried an index match function and successfully pulled the first match row of data (as in, it found the first NOK row, and copied it into the other sheet). I cannot seem to make it find the values after that first row.

Can I do this via excel equations alone? Do I need VBA? If I need VBA, how would that code look like?

Thanks!

 

[Marcelo Branco]

See if this example helps


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Item​

[/td][td]

Status​

[/td][td][/td][td]

NOK List​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Item1​

[/td][td]

OK​

[/td][td][/td][td]

Item2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Item2​

[/td][td]

NOK​

[/td][td][/td][td]

Item4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Item3​

[/td][td]

OK​

[/td][td][/td][td]

Item5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Item4​

[/td][td]

NOK​

[/td][td][/td][td]

Item7​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Item5​

[/td][td]

NOK​

[/td][td][/td][td]

Item10​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

Item6​

[/td][td]

OK​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

Item7​

[/td][td]

NOK​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

Item8​

[/td][td]

OK​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

Item9​

[/td][td]

OK​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

Item10​

[/td][td]

NOK​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in D2 copied down
=IFERROR(INDEX(A$2:A$11,AGGREGATE(15,6,(ROW(A$2:A$11)-ROW(A$2)+1)/(B$2:B$11="NOK"),ROWS(D$2:D2))),"")

M.

 

[Teleporpoise]

Hi M,

Thank you for your response! I think I was unclear in my question though, because what I need is to copy the WHOLE row into another log book, as in another separate workbook. The items that are divided into OK and NOK have other data correlated with them such as date, name of person making decision, etc. Can that still be done?