CODEWITHSUNDEEP
excelvlookup
Luiz
Luiz

Reputation: 85

Vlookup with a predefined and named range

I'm trying to use the VLOOKUP function to a predefined and named range, so, in each column I will look for a different Range, something like this:

       A          B                                C                                 D                               
1     41444     CARS                              VANS                            TRUCKS
2     41445   =VLOOKUP($A2,CARS,2,FALSE)    =VLOOKUP($A2,VANS,2,FALSE)      =VLOOKUP($A2,TRUCKS,2,FALSE)

Where CARS, VANS, TRUCKS are NAMES OF CELL RANGES.

To try to solve this, I used 

 VLOOKUP($A2,INDIRECT(B1),2,FALSE)

but I got #REF as answer. When I use VLOOKUP($A2,VANS,2,FALSE) I get the right result, but I want to refer the table_array by one cell (in case, B1 or C1 or D1). Can Anyone try to help me?

Upvotes: 1

Views: 42928

Answers (5)

Qiu
Qiu

Reputation: 5751

You can get #REF error in two situations.

  1. Vlookup function is define this way:

    VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

    In your sheet you defined Table_array with one column. In the same time you're trying to access 2 column (Col_index_num = 2). But if you say it works without INDIRECT(), it doesn't bother you.

  2. You're using dynamic named range ([example]). For this type of named range, you can't use INDIRECT(). How to bypass it? Check e.g. Referencing Dynamic Named Range in Excel Formula.

Upvotes: 1

Jaycal
Jaycal

Reputation: 2087

Any time you have issues with a formula, use the Evaluate Formula function to step through the formula and see what in fact is causing the problem.

First select the formula that is returning the #REF error, and then in Excel select Formula > Evaluate Formula. Then click the Evaluate Button until you see the #REF error. For your example, if you see something like...

VLOOKUP(2,#REF!,2,FALSE)

then you know the Range you are trying to reference does not exist, or is invalid. To check the defined ranges in your workbook, use the Name Manager (Formulas > Name Manager) and look for the range you expect to see (in this case, you'd be looking for a range with the name of "CARS"). You can update it here and then check the formula again.

Upvotes: 0

pnuts
pnuts

Reputation: 59450

Another approach:

SO17814780 example

The same formula is copied across from B2 to D2. The range named array is shown in G1:J10 but can be on a different sheet or even (with suitable additional reference) a different workbook.

Edit Second example using three distinct named ranges:

SO17814780 second example

In this case the formulae in B2:D2 differ in their second parameter (VANS shown).

Upvotes: 0

Daniel Möller
Daniel Möller

Reputation: 86600

You can effectively name ranges using the text box in the top left of the screen (where appears the "B2" or whatever coordinate the cell has). Select entire column and insert the name there.

Then the name is valid to use in formulas.

Upvotes: 0

ChrisProsser
ChrisProsser

Reputation: 13088

You are very close. Replace the:

=VLOOKUP($A2;INDIRECT(B1),2,FALSE)

with

=VLOOKUP($A2,INDIRECT(B1),2,FALSE)

Upvotes: 1

Related Questions