=IF(ISNUMBER(SEARCH(H3,Sheet1!B:B,0)),INDEX(Sheet1!E:E,SEARCH(H3,Sheet1!B:B,0)),H3)
I have two sheets in my workbook. One sheet has the names as a column with first name and a column with last name. My other sheet has a column with first middle and last name.
I want to search the last name and if you find it return whatever is in column E.
Column H is first middle and last
Sheet1 Column B is last name. And, Sheet1 Column E is the department.
What is wrong with my formula above.
Try:
Please Login or Register to view this content.
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.
It worked better than mine did, however, it's pull the top record for every entry.
Example: If I search for Ray Jones it pull the department on the first line of the sheet. And, it pulls that same infor for every record.
Not sure what that means?Originally Posted by rlkerr
Attach a sample showing expected results.
Please see the attached.
I hope this explains it.
Your data is reverse of what I interpreted from your initial post.
You want to extract the last name and match it to the last name column in another sheet....
You should do this with the help of a helper column..... see attached.
In column Z I extracted the Last Name from column I and then I revised the formula in column Y to look at that extracted name.
Formula to extract name:Formula to find Match:Please Login or Register to view this content.Please Login or Register to view this content.
Yes, That was it.
Thank you. That worked well. Thanks for your time and expertise.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks