I need help with a formula that will refer to data in a cell (within one spreadsheet), look into a second spreadsheet and find the same reference data, then return the value that can lie in one of 7 columns.

The data to reference in the main spreadsheet is located in column B.
In the second spreadsheet, the data to reference is located in column A (the data in this column will not be sorted in ascending order)
Once the data is located in column A, the potential data to return to the main spreadsheet lies in columns C thru I.

I understand a VLookup will not work because 1) the data is not sorted in ascending order (column A) and 2) there is more than 1 column to reference to return the data. I am trying an INDEX and MATCH formula but have been unsuccessful with it as well.

Oh yeah not sure if this will matter but the number of rows to cylce through could be up to as many as 100K. So, a formula that will not take a long time to process is best

Help with this is much appreciated.

Thanks,


mt