find 1 of 3 values on one sheet, from a lookup table on another sheet, enter on 3rd sheet

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?

so do you want to enter myvalue123 in your report sheet?

Yes, myvalue123, or if Sheet1 Column A says "myvalue122" or myvalue124", I want it to choose. Let me rephrase.
sheet1 will have only one of these three values in column A:

myvalue122
myvalue123
myvalue124

but it also has 18466 records in column A. There will generally be three choices for each record on my report (sheet3), the choice of which can be found on Sheet1. I was using a defined name/data validation to pick which one of the three, but I'd rather not have to go looking on the 18466 record sheet1, then pick which one on sheet3. I'd like excel to find one of the three choices on sheet1 based upon the three in the lookup table on sheet2, and insert it into the correct cell on sheet3 where my formula sits. Am I asking to much of software

Hi

use this formula..

Formula:

=IF(COUNTIF(Sheet1!A:A,"myvalue122")>0,"myvalue122",IF(COUNTIF(Sheet1!A:A,"myvalue123")>0,"myvalue123",IF(COUNTIF(Sheet1!A:A,"myvalue124")>0,"myvalue124","")))

Cheers!!

Excelliot, you are a rock star! It worked! Thank you! Now I can go back to being nice to my children

Originally Posted by SLBELL

Excelliot, you are a rock star! It worked! Thank you! Now I can go back to being nice to my children

Glad it worked, Cheers!!