I need to lookup a value from another workbook. I need it to be the last
alpha-numeric value entered in a column. The only thing is that I need it to
pull the value from 3 different sheets with in a work book depending on the
value entered in a cell in sheet 1. Here is an example to help you understand.

Book 1

C2 needs to be the value pulled from the other workbook.

J7 has a posibility of being either silo 1, silo 2, or silo 3.

If J7 equals "silo 1" I need it to pull the last alpha numeric from the
sheet labeled "silo 1" in "book 2"

Help me if you can.