Google Sheets: Vlookup multiple values on different sheets

I’m working with an excel sheet with 11k rows that has some duplicate values that have different amounts. In most cases the duplicates have 2-3 different amounts that I need to review. I want to know if there is a formula that can pull all of the duplicate values and their various amounts from one sheet to another.

Right now I tried using a normal Vlookup formula (=vlookup(a2,sheet1!A:F, 1, false) and it only pulls the first value and its amount that it finds on the table it’s searching. I need a formula that will pull the values and all the different amounts but I can’t quite figure out how to structure the formula.

Hi
First make a list of sheet names somewhere say in range J1:J10
Then in B1 enter

Formula:

Please Login or Register  to view this content.

I used XXX because you did not say in which column the amount is situated, so replace XXX with the column number ( which cannot be 1)
Now pull down as required

Hi Everyone, I really appreciate all of the quick replies but I think I might have to provide some clarity here so the formula works a little bit better.
I attached an example of what the data I'm working with looks like, but essentially I'm working with the same data but it's come from 2 different sources (Interview file data & survey file data). The 3rd sheet in my excel book is for me to audit the 2 different data sources against each other.
On the audit sheet the first 3 columns (A,B,C) only need to be copied over from the respective tab that is listed but the next two columns (D, E) need a formula that will look for column A on sheet 2 and pull whatever the amount & currency that is listed on sheet 2 onto the audit sheet (Sheet 2 titled "survey file data").

I think it's also important to note that I'm working out of Google sheets not excel, so I struggled with the previous formula given because google sheets doesn't have the aggregate formula.
If you all aren't able to help with Google Sheets then I can take an excel specific formula but Google sheets would be the best. All of the help with this is much appreciated since I'm no excel guru!