Complex VLOOKUP with dynamic sheet name

0
votes

I'm trying to perform a vlookup across multiple sheets in an elegant way, rather than with a vlookup nested in an IF function.

Column A has the account number and Column I has the manager responsible for that account. Each Manager has their own sheet. I was hoping to make vlookup look at a specific manager's sheet, find the account number in Column A, and return the info in Column K. I tried to use INDIRECT to make it do that:

=VLOOKUP($A2,INDIRECT($I2 & "!$A:$P"),11,FALSE)

With this code, I keep getting #REF instead of what is in Column K in the manager's sheet. How can I fix this?

Thanks!

1
vbaexcelvlookup
Make sure the text in $I2 has no spaces or extra parts that may make the reference break. Use Trim() - =VLOOKUP(trim($A2),INDIRECT($I2 & "!$A:$P"),11,FALSE) - BruceWayne
I don't know what your data looks like, but keep in mind that vlookup only functions left to right. If your "lookup ID" is to the right of the value that you are looking up, your function will not work. - aLearningLady
@user3578951, there are no leading or trailing spaces so I don't think trim would work - caldrop
@Dr.Trey the first parameter for the VLOOKUP is in Column A, and I'm trying to return the info in Column K, so I should be ok with that. Thanks though! - caldrop

1 Answers

3
votes

I can't see your spreadsheet but if your sheets are named after managers I am willing to bet that those sheet names have some kind of character( like space) that forces referencing sheets in single quotes.

Assuming that is correct simply change your formula to:

=VLOOKUP($A2,INDIRECT("'" & $I2 & "'!$A:$P"),11,FALSE)

Also you can diagnose this issue with "Evaluate Formula" located in "Formulas>Formula Auditing", in there step through all steps that this formula has, and note when does that error shows up.