Hello everyone. I have another weird one for you.

I have 6 columns of data. The first 5 columns have data that needs to be looked up and the 6th is the associated "answer." I need to use a vlookup function to find the "answer." In addition, I have a column with a number that is related to which one of the 5 columns vlookup needs to look in to find a match. The cells contain a 1 through 5, which correspond to columns 1 through 5 mentioned above.

So, I have two worksheets, say A and B. A contains the lookup function, the lookup value, and the number column. Worksheet B contains the 6 columns of data in columns A through F.

I need vlookup to look up a value and it needs to look in the column associated with the “number column.” For instance, if the number is two, I need the function to look in column B and if its 3, it needs to look in column C. I created a lookup function to associate 1 with A, 2 with B, etc, but I cannot get excel to accept the value of the lookup function as a value in the callout for the lookup array in the vlookup function.

I suppose I could do 5 lookup functions, array their values, and use the lookup function from there, but it seems inefficient to do it this way because of the 5 required vlookups rather than 1. I am dealing with 20,000 line items, so I don’t want to bog the computer down any more than I have to. Any ideas?