I have done some digging and found out from others that I should be using a concatenation and a vlookup however I am completely lost, plus all the examples I have seen show the results being produced automatically. I need it done with a VBA macro or subroutine.

edit
Ok I'm still really confused, I have looked up everything and I am feeling really stupid. Can you explain how I can set up this lookup table. This would be set up on another sheet that would not be seen without looking for it. Lets say this sheet is called "Lookup Table". My actual information the example I first put is found on the "Initiating Devices" page. Your saying I would set up the table on "Lookup Table" page. Column A = would be the combined "concatenation" of B2&D2 and column B would be the part number? So example would be Column A = "HeatEST_Add Column B = "SIGA-HRS" Column B result would then be displayed in the "Initiating Devices" page column "E" "Part #" . I guess this is where im lost, because I don't understand how this works. Would it be a VBA macro? The range E7:E2500 would be the range of the values to be displayed. I'm sorry if this doesn't make sense I have a hard time getting this to make sense. Thanks again for any help you can provide.