How do I return a value from another table that appears as a Drop down List

Set up named ranges for each list of names that Buy USD; Sell USD; Buy EUR etc.
The column headers for the lists are placed using advanced filter > unique values only > copy and paste values transpose.
The lists are filled using: =IFERROR(INDEX($C$3:$C$33,AGGREGATE(15,6,(ROW(C$3:C$33)-ROW(C$2))/($E$3:$E$33=I$2),ROWS(C$1:C1))),"")
The source for the data validation list is then: =INDIRECT(SUBSTITUTE(A4," ","_"))
Let us know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

The formula for the 'source' of the data validation drop downs is changed to read:

Formula:

=OFFSET(INDIRECT(SUBSTITUTE(A4," ","_")),0,0,INDEX(Names!$I$1:$AV$1,MATCH(A4,Names!$I$3:$AV$3,0)),1)

Note that the formula references an inserted range I1:AV1 on the 'Names' sheet which is populated using: =SUMPRODUCT(--(I4:I501<>""))
The buy_usd, buy_eur and buy_str columns on the 'Name' sheet have been put in as 'named ranges'. Beginning at 13:21 this video shows how to name ranges more quickly, although I imagine 37 columns with 500 cells per column may take a while.
Let us know if you have any questions.