VLOOKUP 255 cell limit workaround

Joined
Jun 13, 2017
Messages
108
Title says it all - I need help finding a workaround to the cell limit of VLOOKUP.

Let's make it simple:
Column A = Names
Column B = Birthdays.

Cell C1 = Blank cell to input name.
Cell C2 = VLOOKUP of C1 in A:B,2

How do I get this to work with more than 250 entries in the same sheet?
10K, for example?

Thank you!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Re: VLOOKUP 255 cell limit workaround - help

Do you mean your lookup range has more than 255 rows?
There is no limitation on that. Excel can handle that just fine.

If that is not what you are talking about, please explain in more detail, maybe give an example of one that is not working (and post the exact formula you are trying).
 
Upvote 0
Re: VLOOKUP 255 cell limit workaround - help

No matter the number of rows, you can have a million names, in the formula you can put columns A: B and it works.


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:97.9px;" /><col style="width:97.9px;" /><col style="width:97.9px;" /><col style="width:97.9px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >NAME</td><td >BIRTHDAY</td><td >NAME</td><td >RESULT</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >DAM</td><td style="text-align:right; ">10-sep</td><td >DAM</td><td style="text-align:right; ">10-sep</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >JOE</td><td style="text-align:right; ">11-sep</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >EXI</td><td style="text-align:right; ">12-sep</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >PAT</td><td style="text-align:right; ">13-sep</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >SUE</td><td style="text-align:right; ">14-sep</td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >=VLOOKUP(C2,A:B,2)</td></tr></table></td></tr></table>
 
Upvote 0
Re: VLOOKUP 255 cell limit workaround - help

Dante,

I would actually recommend using:
=VLOOKUP(C2,A:B,2,0)

If you leave the last argument off, it does an "approximate" match, and seldom would you ever want an "approximate" birthday if there were no matches.
(i.e. looking up 15-sep in your example would return SUE, even though the 15th is not her birthday!)
 
Upvote 0
Re: VLOOKUP 255 cell limit workaround - help

Vlookup is limited to the number of characters in a cell, not the number of cells
 
Upvote 0
Re: VLOOKUP 255 cell limit workaround - help

Dante,

I would actually recommend using:
=VLOOKUP(C2,A:B,2,0)

If you leave the last argument off, it does an "approximate" match, and seldom would you ever want an "approximate" birthday if there were no matches.
(i.e. looking up 15-sep in your example would return SUE, even though the 15th is not her birthday!)

You're right Joe, I just omitted that argument.
Thanks for the comment.
 
Upvote 0
Re: VLOOKUP 255 cell limit workaround - help

You're right Joe, I just omitted that argument.

No worries!

Just want to make sure that they do not get unintended results...
 
Last edited:
Upvote 0
Re: VLOOKUP 255 cell limit workaround - help


No worries!

Just want to make sure that they do not get unintended results...


Thanks again for worrying.

The correct formula is:

=VLOOKUP(C2,A:B,2,0)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

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