Hi!
I need help with a vlookup formula. I can do a partial match i.e. "=VLOOKUP("*"&A2&"*",$B$2:$B$10,1,FALSE)", but I need to do the opposite of this because I am comparing the column with extra characters against the column with unique id's.
Simply, I need to search for a serial number within a string in a different column. I've attached an example worksheet that should make this more clear.
Thank you!
Blake
Last edited by blakeaces; 10-04-2019 at 12:45 PM.
... and what is supposed to happen in C2 to C10, where there is no 10-digit number in the corresponding cell in column B?
What do you expect to see in C1??
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
What are you wanting to return if you can find the unique ID, and what if you can't?
Pete
Anything, I just want to know if the 10-digit number exists in column A.
If there is a match, return the serial number in column c1. If there is no match, return false or error.
Last edited by blakeaces; 10-04-2019 at 12:08 PM.
Try this in C1:
=IF(COUNTIF($B$1:$B$10,"*"&A1&"*"),"yes","no")
then copy down to C10. The results relate to the code in that row.
Hope this helps.
Pete
Sorry Pete,
I'm not really sure how to explain this concisely. The problem is that the range I need to check is column A, not column B. I need the formula to look at each entry in column A and see if exists in column B.
I've attached a new version of the example sheet to this comment. Hopefully it is more clear. I've also manually done what I want the formula to do.
Thank you for your patience,
Blake
Wait, I think I figured it out. I just switched the range in your formula.
Thank you!
or....
=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(A1,$C$1:$C$10))))>0,"Yes","No")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks