display information based on two cirteria

hi


i am trying to pull data from a table based on two criterias, below is a smaple from the table

A | B | C
ID | Vehicle No. | Status
1 | 1 | DUD
2 | 3 | DUD
3 | 11 | Good
4 | 15 | DUD
5 | 21 | Good


i would like to be able to pull all the Vehicle No's. Data from this table and display it in another table, based on the Status, ie when it is DUD, is there a way to do this without having to manaully reference the vehicle number, ive lloked at vlookup and index/match and if/and function but cant quite work it out.

Please Login or Register  to view this content.

Edit: This is an array formula:...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

The workbook I posted is from your closed thread.

The named ranges are:
Criteria =Sheet2!$F$1
Status =Sheet1!$D$1:$D$16
Vehicle_Number =Sheet1!$A$1:$A$16

Edit:This is an array formula...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Please Login or Register  to view this content.

Modified named ranges to dynamic ranges:
Vehicle_Number =Tables!$J$1:INDEX(Tables!$J:$J,COUNTA(Tables!$J:$J))
Status =Tables!$M$1:INDEX(Tables!$M:$M,COUNTA(Tables!$M:$M))