Hello,
I want to look at a column of data, check if the cell contains text that matches any of the values in another column, if so, return the value in the adjacent column. Sounds easy but I am banging my head against the desk now!
Workbook attached.
Elements.xlsx
Many thanks,
DWSF
Last edited by dwsf; 11-19-2015 at 01:50 AM.
See if this will help?
=INDEX(B:B,MATCH(LEFT(E2,FIND("_",E2,1)-1)&"*",A:A,0))
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Hello,
try in F2 and copy down:
=IF(COUNTIF(E:E,A2&"*"),TRUE)
Edit: sorry, misread the question, just looked at the spreadsheet with FALSE as the result.
cheers, teylyn
thanks fdibbins, this works great, but what if there is no "_" character in the string. Am I barking up the wrong tree with the "search" or "find" functions?
Thanks again,
dwsf
If there is no _ then what else would delimit the text...the 1st space?
If so, then we could maybe replace all _ with space, then search on that?
In F2:
=IFERROR(LOOKUP(2,1/SEARCH($A$2:$A$119,E2,1),$B$2:$B$119),"no match")
searching "Barium" at first position of "Barium_F (ug/L)"
Remove ,1 if searching "Barium" within "XXX Barium_F (ug/L) XXX"
Quang PT
I was trying to get something working using arrays. I have had a lot of success in the past using {=e.g.median(if(array x) = somevalue, (array y), 0) } to parse multiple values but I cant get the syntax to work with search or find, which is what I thought was the way forward because these dont depend on breaking up the cell text based on a particular character.
Cheers,
dwsf
Thanks bebo!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks