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,574
GWCO
  • 31

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,574