3

In Excel, is it possible to name a range that exists over multiple tabs?

For example, I have a range B3:C20 in sheets Tab 1 and Tab 2

I tried to name a range NamedRange1 that referred to 

='Tab1'!$B$3:$C$20,'Tab2'!$B$3:$C$20

which seemed to work successfully.

However, when I did a vlookup =VLOOKUP(F2, NamedRange1, 2, 0) I received a #VALUE! error.

Am I doing something wrong? Both values F2 and the NamedRange1 have value types of text.

yass
  • 2,534
  • 3
  • 11
  • 18
GWCO
  • 31
  • 1
  • 3
  • 1
    It's like calling two different kids named John. Which one did you call? No wonder Excel got confused and threw an error. Can you show us a sample of what you want to achieve? Give us a screenshot of desired output, and we'll see how to achieve that. – Vylix Jun 29 '17 at 18:30
  • Your `VLOOKUP` formula is missing parentheses. Did you copy pasted the formula directly from Excel? That might be the problem. – Vylix Jun 29 '17 at 18:35

1 Answers1

2
  • Select B3:C20 in Tab1 and in the upper left box (cell address) write:
    Tab1!NamedRange1
  • Select B3:C20 in Tab2 and in the upper left box (cell address) write:
    Tab2!NamedRange1

Now use: 

=Iferror(Vlookup(F2,Tab1!NamedRange1,2,0),Vlookup(F2,Tab2!NamedRange1,2,0))

You cannot define the same name in each sheet but when you write the sheet name in the range name you can use it

yass
  • 2,534
  • 3
  • 11
  • 18