Hi, I have searched a lot and I am not able to find a solution for this. I am hoping you guys can help me
I need to look for a Value (column A) in a String array (column B) and to return an adjacent Value (column C). This formula works for me when the the String array (column B) is kinda short.
=VLOOKUP("*"&A1&"*",B:C,2,FALSE)
But if the String array (column B) is huge I get a #N/A.
I think there must be a limit for the "Concatenation". What do you guys think? Thanks!
How huge? The limit for text strings inside a formula is 255 characters.But if the String array (column B) is huge I get a #N/A.
Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).
1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.
2. Make sure that your desired solution is also shown (mock up the results manually).
3. Make sure that all confidential information is removed first!!
4. Try to avoid using merged cells. They cause lots of problems!
Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Thanks Raphaelp, most of the Strings in my file have around 1,700 characters (including spaces). Is there a way to ignore the 255 char limit?Originally Posted by Raphaelp
I have attached an example as Glenn advised; As you can see one VLOOKUP is working and the other one is not due to the size of the String.
I don't think that you have provided enough information yet... Two rows where A1 is included within B1 may not reflect reality..
But... try this in F2, copied down:
=IF(ISNUMBER(SEARCH(A2,B2)),OFFSET(A2,,2,,))
This seems to work, as an array formula:
=INDEX($C$2:$C$3,MAX(ROW($1:$2)*NOT(ISERROR(FIND(A2,$B$2:$B$3)))))
Pauley
--------
If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).
You are right Glenn, A1 could be anywhere in column B. Your Formula works if both Values are on the same row. Sorry for lack of info, I didn't want to bother you guys with a huge file.
Thanks Pauley, this seems to return C1 if A1 was found anywhere in B. I would need (for example) C99 if A1 was found on B99.
Or try:
=LOOKUP(2,1/FIND(A2,$B$2:$B$10),$C$2:$C$10)
Hmm, thought I tested for that. Did you enter it as an array formula (Ctrl Shift Enter)? @Phuocam's solution may be better since it is not an array formula.
Last edited by Pauleyb; 09-21-2017 at 10:41 AM.
To be honest I didn't know I had to hit ctrl+shift+enterit worked, thanks!
This also worked and looks simple, thanks Phuocam!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks