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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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