excel vlookup column limit?

kwg

Board Regular
Joined
Apr 26, 2003
Messages
75
Is there a limit to the number of columns one can reference in excel Vlookup? I can get numbers from a table up to 26 columns but when I try to get any other column greater than 26 I get the #REF error. Thank you Happy new year! :rolleyes:
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
santeria said:
santeria...I really don't see how a thread about the limitations of XL's column count corresponds to the OP's questions about VLookups? :unsure:

As far as the OP's question...from what I've seen there is not a "limitation" on the column that a VLookup can return. If you are getting a #REF error, it is because you are trying to return a column outside of your table_array.

For example...
Book5
ABCD
13Tommy3
2#REF!MrExcel1
3Aladin2
Sheet1


The formula in A1 is =VLOOKUP("Tommy",C1:D3,2)
The formula in A2 is =VLOOKUP("Tommy",C1:D3,3)

Now, the second formula is returning an error because I am trying to return column 3 of a table that only contains 2 columns.

Also...this is quoted directly from the XL Help documentation...
Microsoft Excel Help said:
Col_index_num is the column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is less than 1, VLOOKUP returns the #VALUE! error value; if col_index_num is greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.
 
Upvote 0
I had thought so, but obviously I was wrong ...

(y)

TommyGun said:
santeria...I really don't see how a thread about the limitations of XL's column count corresponds to the OP's questions about VLookups? [/quote]
 
Upvote 0
Thank you for the help everyone-problem is resolved I expanded the vlookup range as recommended in replies. Happy New Year!
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top