Vlookup with a predefined and named range

Question

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?

Yes, this formula that you wrote gets the result of VANS. And the result is the number 0,07, for example. — Luiz, Jul 23 '13 at 17:14
Sorry for misunderstanding. What I mean is that B1, C1 and D1 has the defined name of an Array, that is defined in another Sheet. So, I defined the names "Cars", "Vans", "Trucks" for 3 different matrix and I want that each column VLOOKUP in one of those tree. — Luiz, Jul 23 '13 at 18:28
Thats definitelly no problem. First of all, you are helping me and i'm very thankful. Second, I not sure if I understood your questioning, but CARS is a range like "=OFFSET(base!$E$2;0;0;base!$O$8;2)", in the sheet "base". — Luiz, Jul 23 '13 at 18:54
If you could put an image of your sheet structure (from where you are getting the cars, vans, etc, it would be some help) — Daniel Möller, Jul 23 '13 at 19:08
Oh, now I saw. But I have to upload the image in a website first, right? Because it's not uploaded, it's on HD — Luiz, Jul 23 '13 at 19:30

score 1 · Answer 1 · answered Jul 23 '13 at 15:48

1

You are very close. Replace the:

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

with

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

answered Jul 23 '13 at 15:48

ChrisProsser

12,598
6
35
44

1

Or everything with `;` depending on his excel settings. – Daniel Möller Jul 23 '13 at 15:49
I'm srry, it was a type error !! I just edited now! Still with the problem! – Luiz Jul 23 '13 at 15:50
That's strange. I have done a quick test with the above (creating small named ranges for CARS, VANS and TRUCKS) and this works for me. Maybe just check that they are both number fields and you don't have text for one set e.g. in the named ranges and number for the other. – ChrisProsser Jul 23 '13 at 16:44
Yes, that's very strange. The fact of the range VANS, CARS and TRUCKS are in another PLAN means something? Remember, it's in the same WORKBOOK – Luiz Jul 23 '13 at 16:54
I check also the contents. But thats not the problem. When i use 'VLOOKUP($A2,VANS,2,FALSE)', I can get the right result – Luiz Jul 23 '13 at 16:59
This works for me regardless of whether they are in the same sheet or not. What do you mean by PLAN? Also is B1 'CARS' or 'VANS'? – ChrisProsser Jul 23 '13 at 17:01
Sorry, by "plan" I mean Sheets. About CARS or VANS, this really didnt make much difference, but B1 is CARS. My mistake. – Luiz Jul 23 '13 at 17:13

score 1 · Answer 2 · edited May 23 '17 at 12:15

1

You can get #REF error in two situations.

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.
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.

edited May 23 '17 at 12:15

Community

1
1

answered Jul 23 '13 at 16:23

Qiu

5,651
10
49
56

Acctually, the Table_array over there is the NAME OF THE CELL RANGE, it's not that cell only. For example, the range cell VANS is a 100x2 matrix in another PLAN. – Luiz Jul 23 '13 at 16:57
@Luiz: I was referring to this: VLOOKUP($A2,INDIRECT(B1),2,FALSE) – Qiu Jul 23 '13 at 17:28
Yes, i want to 'indirect(b1)' refers to the array named in B1 – Luiz Jul 23 '13 at 17:32
Nope, I havent used INDEX,how would you use? – Luiz Jul 23 '13 at 18:22
Now they are generated by myself (typing), but I want them to be transposed from another sheet – Luiz Jul 24 '13 at 13:14
And one final question - are you using dynamic named range? – Qiu Jul 24 '13 at 13:22
I'm sorry, but what exactly is this? – Luiz Jul 24 '13 at 13:40
@Luiz: maybe now it will be helpful for you. – Qiu Jul 24 '13 at 13:52
Thanks, I just saw the links you added. But that question do not have an answer as mine, right? – Luiz Jul 24 '13 at 14:19

score 0 · Answer 3 · answered Jul 23 '13 at 15:48

0

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.

answered Jul 23 '13 at 15:48

Daniel Möller

84,878
18
192
214

Yes, acctually, I have done it already, I named my cell ranges to VANS, TRUCKS and CARS, and I want to acess them by their names! – Luiz Jul 23 '13 at 16:57
Please check if you named a single cell or if you named the entire column. Vlookup will only work with columns. – Daniel Möller Jul 23 '13 at 19:02
I found the flaw....in fact, vlookup works only if the passed index is inside the range, so a single column does not suffice. You would have to name a range containing the searched value AND the desired result. You could change that to a `Match` and `Index` approach, those would accept single columns (not single cells) – Daniel Möller Jul 23 '13 at 19:05
Actually, what i'm looking for is an date, and it's inside the range. I just edited my example right know. Sorry for misunderstanding – Luiz Jul 23 '13 at 19:09

score 0 · Answer 4 · edited Jun 20 '20 at 09:12

0

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).

edited Jun 20 '20 at 09:12

Community

1
1

answered Jul 23 '13 at 16:30

pnuts

58,317
11
87
139

Sorry, I didn't really undestand what you are explaining. Which range "array"? And which G1:J10? – Luiz Jul 23 '13 at 16:55
Yes, I did it. The names VANS CARS and TRUCKS are defined names of cell ranges. But it did not work ! – Luiz Jul 23 '13 at 17:19

score 0 · Answer 5 · answered Jul 23 '13 at 18:55

0

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.

answered Jul 23 '13 at 18:55

Jaycal

2,087
1
13
21

Thanks a lot, gonna look for it now! – Luiz Jul 23 '13 at 18:57
I got exactly the error you tell... 'VLOOKUP(2,#REF!,2,FALSE)'. After looking in name Manager, I check the my range DOES EXIST ! Thats very akward! – Luiz Jul 23 '13 at 19:03
So when you say it exists, you're saying (1) the name in the Name column is "CARS", (2) you can see all the values of CARS in the Value column, (3) the Refers To column has the location of those cells (i.e. something like Sheet1!$A$1:$B$4), and (4) the Scope column has "Workbook"? – Jaycal Jul 23 '13 at 19:13
Yes, to almost all questions. (1), (3) and (4) yes. Also, @pnuts, the scope refers to workbook. But the (2) the values are "{...}", but I think it's because CARS isn't only one column – Luiz Jul 23 '13 at 19:20
Expand the Value Column as far as you can; it should show at least some of the values. If it does not, then you'll want to click the blueish button to the right of the textbox at the bottom of the Name Manager window to go to the exact location where Excel is referring to when it uses "CARS". There, make sure the data is as expected – Jaycal Jul 23 '13 at 19:27
Yes, as you said, it does not show anything and when I go to the exact location, it's perfect. Any more ideas? – Luiz Jul 23 '13 at 19:29
Does `VLOOKUP($A2,CARS,2,FALSE)` (typing CARS instead of using the cell reference) work? if so, check B1 (the cell with the text "CARS" in it) and make sure there are no spaces **before and after** the text – Jaycal Jul 23 '13 at 19:37
Yes, if I type the matrix name it works... also, just checkd and B1 is just like the matrix name... that's getting too boring! – Luiz Jul 23 '13 at 19:46
Tricky one indeed! Only other thing to say is to actually click the cell that has "CARS" in it and make sure there are no spaces before and after the text (or if it's a formula, the source of the word CARS doesn't have extra spaces). If not and all the other things we talked about you have confirmed, I'm not sure why else this would not work for you.... – Jaycal Jul 23 '13 at 19:57
Yeah, i'm very thankful for your job, but I still have the same problem. Thanks anyway – Luiz Jul 24 '13 at 13:13

Vlookup with a predefined and named range

5 Answers5