Search for text in 1 column and return text from another
Hello,
So I've got col. A and B. I want to search the text in A, but without the last 5 char. in Col. B. Here's that code which works fine.
But now instead of displaying an X, I want to pull the last 5 characters from column B for which the code above would have normally put an X.
=IF(COUNTIF(B:B,"*"&LEFT(A2,LEN(A2)-5)&"*"),"x","")
So, Conceptually I have this which doesn't work.
=IF(COUNTIF(B:B,"*"&LEFT(A2,LEN(A2)-5)&"*"),RIGHT(B:B, 5),"")
Last edited by madadd; 03-09-2011 at 01:22 PM.
Is there only one match? or are you expecting multiple returns from column B?
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Oh, um there might be more than one, but I really only need one. Honestly whichever is easiest to do.
This would return the 1st match it comes across, if any:
=IF(ISNUMBER(MATCH("*"&LEFT(A2,LEN(A2)-5)&"*",B:B,0)),RIGHT(INDEX(B:B,MATCH("*"&LEFT(A2,LEN(A2)-5)&"*",B:B,0)),5),"")
Ah sweet, you're awesome. Thank you very much!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks