Index match from multiple worksheets based on dependent cell

Hey all,

I have been looking through the forums, and found a few examples of this problem, but nothing I've been able to make work so far because of the dependent cell. I have three worksheets (Segment_1, Segment_2, Segment_3). Each worksheet has a set of unique IDs on them. I need to be able to look up the segment that has been selected on a fourth tab, and based on that value, pull all of the ID numbers and associated names.

I have attached a document and wrote an example of the problem on it. There is another problem included in the file as well that does something similar but the other way. Based on the multiple testing tabs, it looks up which ones contain the ID numbers and returns the average values.

Thanks,
B