Hi, i'm new at this forum, and i have difficulties with a formula in my worksheet.
I have a excel sheet with 3 rows.
In row A i have about 1000 customer numbers.
In row B i have their email adresses.
In row C i have about 500 of the 1000 customers.
I want to match the customers in row C with the customers in row A, and then extract their email adresses.
Or if you see it in another way, i want to remove the customers in row A that does not match the customers in row C. I would then see which 500 customers that are left, and i would have their email adresses displayed in row b.
The whole clue with my question is that i want to find out the email adresses of the 500 customers in row C.
If anyone can help me with this, i am very grateful.
Try this in D1 and pull down:
=IF(ISNA(MATCH(C1,$A$1:$A$1000,0)),"",VLOOKUP(C1,$A$1:$B$1000,2,FALSE))
"Relax. What is mind? No matter. What is matter? Never mind!"
Doesn't work, Excel marks that something is wrong with the formula. It marks the C1,$A$1 values after MATCH of the formula.Originally Posted by zbor
I use excel 2007, if that has something to do with it. I also use english excel.
Does your version use semicolons instead of commas as separators? I don't see anything wrong with zbor's formula, save that it could be shortened to =IFERROR(VLOOKUP(C1,$A$1:$B$1000,2,0),""). That puts the email addresses for your customers in column C into column D (or wherever you put it).
Life is about perseverance. Remember: today's mighty oak tree is just yesterday's nut that held its ground.
If you like a post, please rate it with the scales icon (top right).
If you feel really indebted, please consider a donation to charity, such as Feed the Children or Habitat for Humanity.
Ah, yeah, it looks like my version uses semicolons. I tried the formula you wrote, but it leaves empty cells. I have yet to mention that the list in row A and row C isn't sorted alfabetically, if that has anything to do with it. The list in row C looks more random than row A.
Doesn't mater. Only need to have exact match.
"Relax. What is mind? No matter. What is matter? Never mind!"
It will leave empty cells if no match is found. Keep in mind that VLookup is a very sensitive formula. "John Smith" and "John Smith " (notice the space at the end) are not the same thing. Any blank you see means that the corresponding cell does not appear exactly in column A.
Life is about perseverance. Remember: today's mighty oak tree is just yesterday's nut that held its ground.
If you like a post, please rate it with the scales icon (top right).
If you feel really indebted, please consider a donation to charity, such as Feed the Children or Habitat for Humanity.
oh ok. then i need a formula that will delete all characters from the cells, and just leave the numbers.
I guess the formula will work like a charm when this is done
Wait...I thought you had numbers in A and emails in B. I'm confused. Could you please post an example workbook with the exact format (albeit dummy data) you're working with?
Life is about perseverance. Remember: today's mighty oak tree is just yesterday's nut that held its ground.
If you like a post, please rate it with the scales icon (top right).
If you feel really indebted, please consider a donation to charity, such as Feed the Children or Habitat for Humanity.
Ok, here's an example which looks like my original sheet. Row C contains both the name of the customer, and the customer id. Row A contains only the customer id. I guess if i can get a formula to extract the numbers from row C, then i can use one of the formulas above to connect customer id's from row A and C, to emails in row B ?
Row C is an exact copy of my original sheet, with the same amount of spaces between characters and numbers.
Hello evisu,
Try any of this in D1, copy down.
=IFERROR(LOOKUP(9E300,SEARCH(A$1:A$14,C1),B$1:B$14),"")
Or,
=IFERROR(LOOKUP(9E300,MATCH("*"&A$1:A$14&"*",C1,0),B$1:B$14),"")
HTH; Haseeb
If your problem is solved, please say so clearly, and mark your thread as Solved:
Forum Rules & How to Mark a thread as SOLVED
one way to extract the numbers is
=LOOKUP(9.99E+307,--MID(C1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},C1&1234567890)),ROW(INDIRECT("1:"&LEN(C1)))))
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
If the ID is always six digits, replace C1 with Right(C1;6) in the formula I offered and it should work.
Life is about perseverance. Remember: today's mighty oak tree is just yesterday's nut that held its ground.
If you like a post, please rate it with the scales icon (top right).
If you feel really indebted, please consider a donation to charity, such as Feed the Children or Habitat for Humanity.
Absolutely awesome. I'm almost there now. It works excellent in the dummy sheet. In the dummy sheet, the values in row A is formatted as text. With the green marker on each cell.
In my original worksheet, excel just won't let me convert row A with the numbers to "text". I want the green markers. Then it will work.
Right(C1;6)+0 will convert the values in C to numbers, which should then match up with the values in A.
Life is about perseverance. Remember: today's mighty oak tree is just yesterday's nut that held its ground.
If you like a post, please rate it with the scales icon (top right).
If you feel really indebted, please consider a donation to charity, such as Feed the Children or Habitat for Humanity.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks