# vlookup using named ranges

##### Board Regular
Rather than use column numbers in my vlookup I have created a lot of named ranges to use instead but I am struggling to get it ti work - in the formula below I get #REF, if I substitute "full_range_student_id" with its column number (in this case 2) it works... Is it not possible to use a name as the col_index_num?

I've pasted in what I think is relevant below:

=VLOOKUP(A3,aut,full_range_student_id,FALSE)

 aut =Aut!\$A\$3:\$FP\$250

<tbody>
</tbody>

 full_range_student_id =Aut!\$B38:\$B\$250

<tbody>
</tbody>

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
And which cell in =Aut!\$B38:\$B\$250 is the Vlookup meant to be taking the column Index Number from?

The one adjacent to the one that matches A3 (or A4 or whatever down the column)... in range "aut"...

I don't understand the question? Am I missing something?

...and I misposted - it should be =Aut!\$B\$3:\$B\$250 (i.e. not \$B\$38) and I have tried =Aut!\$B:\$B too...

Last edited:
I think you want

=VLOOKUP(A3,aut,COLUMN(full_range_student_id),FALSE)

you still need to put the column number it needs to look in

=VLOOKUP(A3,aut,full_range_student_id,2,FALSE)

OK I think I understand now - you can't do that with VLOOKUP (it expects a positive integer value in the third argument corresponding to the column number), however, you can use INDEX/MATCH:

=INDEX(full_range_student_id,MATCH(A3,INDEX(aut,0,1),0))

The INDEX(aut,0,1) is needed in the MATCH as MATCH only works with single column or row ranges (INDEX(aut,0,1) says return all the rows of aut column 1)

I'm being really dumb not framing my question properly I think...! I want to replace the "2" (column number) with the name I gave the same column...

OK - thanks - I will try that...!

Ah, Not sure you can do that. I am sure it will require a number in the formula.

I think you want

=VLOOKUP(A3,aut,COLUMN(full_range_student_id),FALSE)

This works - thanks Jonmo1...

Replies
3
Views
299
Replies
1
Views
176
Replies
6
Views
272
Replies
3
Views
197
Replies
0
Views
184

1,206,921
Messages
6,075,589
Members
446,147
Latest member
homedecortips

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back