Looking to be able to search column A for a persons name and return the value stored in the cell next to it The name I want to find is in this format "Robert Kukulka"
Challenge is the names in column A also includes numerical data I need to ignor.
Column A Column B
1098676 - Kukulka, Robert 45
1098856 - Lewellen, Stan 22
1098904 - Big, Edward 30
Cant seem to find anything that works reliably. Any suggestions would be most appreciated!
Here is a sample data sheet
You mean search column B for name then return coresponding in column A?
Try:
=LOOKUP(2,1/ISNUMBER(SEARCH("Robert",$B$2:$B$6)*SEARCH("Kukulka",$B$2:$B$6)),$A$2:$A$6)
Quang PT
Put in H3:
=INDEX(B3:B5,MATCH(H2,MID(A3:A5,FIND(",",A3:A5)+2,50)&" "&MID(A3:A5,SEARCH("-",A3:A5,1)+1,SEARCH(",",A3:A5,1)-SEARCH("-",A3:A5,1)-1)))
Array Formula (You need to confirm press CTRL-SHIFT-ENTER button together)
In the attached sample, I want to search column A for the value in H2, then return the value from Column B
Thank you!!
see the file
try this
=VLOOKUP("*"&RIGHT(H2,LEN(H2)-FIND(" ",H2))&", "&LEFT(H2,FIND(" ",H2)-1),$A$3:$B$5,2,0)
Last edited by AlKey; 02-18-2014 at 11:30 PM.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
So update the formula to adapt with your attachment
Please Login or Register to view this content.
Missed something
=INDEX(B3:B5,MATCH(H2,TRIM(MID(A3:A5,FIND(",",A3:A5)+2,50)&" "&TRIM(MID(A3:A5,SEARCH("-",A3:A5,1)+1,SEARCH(",",A3:A5,1)-SEARCH("-",A3:A5,1)-1))),0))
Last edited by azumi; 02-18-2014 at 11:21 PM.
Thanks guys...
azumi's
=INDEX(B3:B5,MATCH(H2,TRIM(MID(A3:A5,FIND(",",A3:A5)+2,50)&" "&TRIM(MID(A3:A5,SEARCH("-",A3:A5,1)+1,SEARCH(",",A3:A5,1)-SEARCH("-",A3:A5,1)-1))),0))
Works great for my application!
Update formula for name in H2:
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks