LinkBack URL
About LinkBacks
I want to do a VLOOKUP but don't want to comapre the whole cell just the first part before a '_' charachter.
Eg: cell A = TOM_SMITH
Cell B = TOM_SMYTH
would be a match - so I'm ignoring everything after the first _
Thank you.
I want to do a VLOOKUP but don't want to comapre the whole cell just the first part before a '_' charachter.
Eg: cell A = TOM_SMITH
Cell B = TOM_SMYTH
would be a match - so I'm ignoring everything after the first _
Thank you.
If your lookup value is in A1 and your lookup table is in E1:F4 where E1:E4 contain the data to match and F1:F4 the return results, then something like this should work.
=INDEX(F1:F4,MATCH(LEFT(A1,FIND("_",A1)-1),LEFT(E1:E4,FIND("_",E1:E4)-1),0))
adjust ranges to suit.
Note: You must confirm the formula with CTRL+SHIFT+ENTER not just ENTER. You'll see {} brackets appear around the formula.
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.
Thanks, I'll give it a try.
My original VLOOKUP is this: =VLOOKUP(F95,[Form_results_07Aug2007.xls]Sheet1!$F:$J,5,0)
Try just
=VLOOKUP(LEFT(F95,FIND("_",F95))&"*",[Form_results_07Aug2007.xls]Sheet1!$F:$J,5,0)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks