need vlookup formula help

[MikeMann126]

Does anyone know how to get the vlookup formula to search for a lookup_value that is a formula with a text string in it? trying to get vlookup to find ="total revenue"&rept(".",100) keeps showing either #!NA of #!value. I also tried using concatenate ("total revenue"&rept(".",100) to no avail. any help would be appreciated.

 

[Luke M]

It should work. Would you mind posting your entire formula so we can check it?

The other problem that is probably happening is in your raw data. When you enter 3 .'s, XL autocorrects them into an ellipsis symbol, which XL does not count as being equal to "...", and the lookup will fail.

 

[Somendra Misra]

Hi MikeMann,

=VLOOKUP("total revenue"&REPT(".",100),B3:C5,2,0)
This formula is working. I had set up my table in B3:C5. It fetch the second column value.

Correct me If I did not understood your problem.

Regards,

 

[MikeMann126]

see the highlighted cells in the attached file. this might help.

 

[Somendra Misra]

@MikeMann126

Your are using REPT(".",300), which will exceed the limit of 255 character in a cell. I change it to 100. It fetched the result.
Go through below link to know EXCEL specifications.
http://office.microsoft.com/en-in/excel-help/excel-specifications-and-limits-HP010073849.aspx

Regards,

 

[MikeMann126]

Thanks Somendra for the quick response and solution. Such an easy fix.

 

[Somendra Misra]

Welcome anytime.

 

[Luke M]

For posterity sake, other info...
The Excel specifications link doesn't specifically call out the 255 character limit for a cell. It seems to be an issue with the lookup type functions. If the need arises where you need to use > 255 characters, a combination of INDEX and MATCH will work.
http://stackoverflow.com/questions/...xcel-vlookup-function-limit-of-256-characters