Trying to match multiple criteria and return a corresponding value

Hi, I'm trying to create a summary sheet ("Trade Sheet") that will pull wire instructions from a worksheet ("Wire Instructions") based on multiple matching criteria of a trade listed on the summary sheet. Attached is an example of what I'm trying to do. I have no idea where to start, can someone help?

Thanks in advance!

Book9.xlsx

Here's what I came up with. As long as the data is consistent (eg: the order of company information doesn't change with each company) it will work everytime.

B18:

Please Login or Register  to view this content.

Copy down

B26:

Please Login or Register  to view this content.

Copy down

Originally Posted by Qualo_Jinn

Here's what I came up with. As long as the data is consistent (eg: the order of company information doesn't change with each company) it will work everytime.

B18:

Please Login or Register  to view this content.

Copy down

B26:

Please Login or Register  to view this content.

Copy down

Hi, I'm getting circular references with both formulas?

i would suggest you to put the information in the Wire Instruction Sheet in a tabular format and then use the index and match

Put the formula next to Bank, not Company. The company name should populate from what is input at the top. The formula then reads that and gathers the required infomation.

Vinoth also makes a good suggestion. Putting the information in a chart form will make the formula 'safer' to use.

For example.

Across the top row:
Company | Bank | Acct #/IBAN | Currency | Swift/ABA | Region

And make a sheet for TO and FROM.

I don't know how easy that would be for you to do.

The same thing for the Wire From information. 'Fund' will be populated from the original input so put the formula next to 'Fund Ticker'.

Thanks for the suggestions, I think I may be able to use either method now that I'm using your formula correctly.

I'm still having some issues, though this may be a different can of worms. I'm using the suggestion to lay out the Wire Instructions in tabular format. So now I am trying to create a formula to populate the instructions but there are 2 possible instructions for Wire To (Purchase Wire To and Redemption Wire To), and 2 possible instructions for Wire From (Purchase Wire From and Redemption Wire From). Is it possible to use an IF statement along with Index/Match to do this? I updated my example to clarify.

Book9.xlsx