vlookup using named ranges

[ad1234]

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>

Many thanks in advance...

Ad1234

 

[Firefly2012]

And which cell in =Aut!$B38:$B$250 is the Vlookup meant to be taking the column Index Number from?

 

[ad1234]

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

 

[Jonmo1]

I think you want

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

 

[Dryver14]

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

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

 

[Firefly2012]

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)

 

[ad1234]

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

 

[ad1234]

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

 

[Dryver14]

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

 

[ad1234]

I think you want

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

This works - thanks Jonmo1...