This is so hard to explain! Thanks to anyone who responds!

I have a workbook with multiple sheets. One sheet has unique data in column A that I want to show up on my report page.

Sheet 1, column A

Name

other text starting at a4
myvalue123
other text ending at a18466

Sheet 2 has a lookup table that contains values in B2:B4

defined name is Sheet2lookup

myvalue122
myvalue123
myvalue124

Instead of me clicking the down arrow in my report and picking which one of the three, I want excel to look at my master sheet1 that has the actual data in it, find which one of the three values it has, and insert it into my report on sheet3

Is there a way to do this?