CODEWITHSUNDEEP
vbaexcelvlookup
caldrop
caldrop

Reputation: 17

Complex VLOOKUP with dynamic sheet name

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!

Upvotes: 0

Views: 3656

Answers (1)

sgp667
sgp667

Reputation: 1875

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.

Upvotes: 3

Related Questions