Lookup Macro

**IwannabanExcelGod** · 03-03-2012, 09:20 PM

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

**DGagnon** · 03-03-2012, 09:22 PM

is there a criteria for which of the columns to pull the data from? do you have a sample data set?

**marreco** · 03-03-2012, 09:22 PM

post your Attachiment

**FDibbins** · 03-03-2012, 09:38 PM

a vlookup will work on unsorted data if you add the ,false condition at the end. However, finding it in 1 of many different columns is anoter problem. Is there anyway to easily determine which column it is in, or would that need to be part of the formula?

**IwannabanExcelGod** · 03-03-2012, 10:00 PM

Sample attached.

Sheet 1: Representation of the spreadsheet that where the formula will reside.
Sheet 2: Representation of the spreadsheet where the source data will reside

Although it appears the data in columns C thru I would be consistent in each column, it will not be.

Hopefully this will help.

mt

**DGagnon** · 03-03-2012, 10:06 PM

what do you do in the case of the data being in multipul columns?

**IwannabanExcelGod** · 03-03-2012, 10:19 PM

So far (from the data I have been given) data will only lie in one column per item. However, I cannot gaurantee this will not occur at some point. If this was the case, would concatenation be the best alternative - which would allow a vlookup to work as designed (since the column number would be known)? If this would be the case, I would need to understand how to concatenate prior to running the lookup (from outside the source file).

**DGagnon** · 03-04-2012, 02:50 AM

there may be a more effecient way to do this, but this will work, and it will concatenate if there are multipul matches in one line

Please Login or Register  to view this content.

it would run quicker on large ammounts of data if you add a helper column that contains =MATCH(A2,Sheet2!A:A) and just use the bellow, which is just a shortned down version. it is faster because the match will not need to be calculated as many times:

Please Login or Register  to view this content.

**DGagnon** · 03-16-2012, 10:21 PM

Is your issue resolved? If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.