Single named range over multiple sheets in same Excel file?

3

1

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.

GWCO

Posted 2017-06-29T18:20:19.313

Reputation: 31

1It'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 – 2017-06-29T18:30:08.213

Your VLOOKUP formula is missing parentheses. Did you copy pasted the formula directly from Excel? That might be the problem. – Vylix – 2017-06-29T18:35:39.007

Answers

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

Posted 2017-06-29T18:20:19.313

Reputation: 2 409

Asked: 2017-06-29T18:20:19.313

Viewed: 2 188 times

Active: 2019-02-05T23:38:16.710