0

I AM USING GOOGLE SHEETS, NOT SURE IF THIS IS THE BEST PLACE TO POST BUT FOR CLARIFICATION!

Hi there, I am attempting to index the contents of a cell and use that as the range in my VLOOKUP formula.

The formula is

=VLOOKUP(B4,'B2'!1:130,2,FALSE)

Explaining the formula... The search key is the Employee Name which comes from a dropdown in B4. The Range is B2, which is a dropdown for my four sheet names (Test1, Test2, Test3, Test4) The idea is to index column 2 in the sheet value of B2 (test1,2,3 or 4) and return the result of Sam (employee name).

Every time I try, it returns 'Unresolved sheet name B2.

I have tried using INDEX(B4) in replacement of B4 but still no luck. Attached is a photo for reference.

Photo Reference

Improve this question

2 Answers 2

Reset to default
0

it returns 'Unresolved sheet name B2.

This is because you don't have a sheet named "B2". You are actually trying to refer to the sheet named "Test1" which is the value in cell B2.

There's also a slight problem with the VLOOKUP range of "1:130" - normally this range will include the column references as well.

The solution is to use a combination of ADDRESS (which will allow you to use the value in Cell B2 as the 'Sheet name') and INDIRECT (which will convert the string created by ADDRESS into a cell reference).

ADDRESS

  • Enter this in Cell D1: =address(1,1,4,1,B2)
    • The result will be as string - "Test1!A1"
  • Enter this in Cell D2: =address(10,4,4)
    • The result will be a string - "D10"
  • Combine them in Cell D3: =address(1,1,4,1,B2)&":"&address(10,4,4)
    • The result is a string - "Test1!A1:D10"

COMBINE INDIRECT WITH VLOOKUP

  • Enter this in Cell C6: =vlookup(B4,indirect(address(1,1,4,1,B2)&":"&address(10,4,4)),2,0)
  • The result will be the value in the column B of sheet="Test1", the cell directly adjacent to the value = "Sam"

SAMPLE

result

SAMPLE DATA

data

Improve this answer
0

Seems to me it should be as simple as this:

=VLOOKUP(B4,INDIRECT(B2&"!A:B"),2,FALSE)

... or if you are going to be requesting other info from the row...

=VLOOKUP(B4,INDIRECT(B2&"!1:130"),2,FALSE)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.