search multiple sheets, return multiple values?|General Excel Questions & Answers|Excel Forum|My Online Training Hub

Hello,

I am looking to input a number and have a formula search multiple sheets for that number in column A, and return all values in column B associated with the input. I have my sheets named Book 1, Book 2, etc. Under the DWG # column you will see that some numbers are repeated in the same sheet and on other sheets. For example, DWG # 8306305 appears on Book 1 (twice), on Book 2 (once) and Book 3 (once). I would like to have all the corresponding ECN #'s be outputted. For example, input 8306305 and in the next column I want to see 2, 145, 621, 700. It doesn't matter if the ECN #'s are in the same cell or positioned vertically. As you will see I have figured out how to return the very first ECN # of the inputted value, however, I need all of them. I have hit a dead end with google. Thank you!