match values

[evisu]

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.

[zbor]

Try this in D1 and pull down:

=IF(ISNA(MATCH(C1,$A$1:$A$1000,0)),"",VLOOKUP(C1,$A$1:$B$1000,2,FALSE))

[evisu]

Try this in D1 and pull down:

=IF(ISNA(MATCH(C1,$A$1:$A$1000,0)),"",VLOOKUP(C1,$A$1:$B$1000,2,FALSE))

Doesn't work, Excel marks that something is wrong with the formula. It marks the C1,$A$1 values after MATCH of the formula.

I use excel 2007, if that has something to do with it. I also use english excel.

[darkyam]

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).

[evisu]

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).

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.

[zbor]

Doesn't mater. Only need to have exact match.

[darkyam]

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.

[evisu]

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

[darkyam]

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?

[evisu]

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?

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.

[Haseeb Avarakkan]

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),"")

[martindwilson]

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)))))

[darkyam]

If the ID is always six digits, replace C1 with Right(C1;6) in the formula I offered and it should work.

[evisu]

If the ID is always six digits, replace C1 with Right(C1;6) in the formula I offered and it should work.

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.

[darkyam]

Right(C1;6)+0 will convert the values in C to numbers, which should then match up with the values in A.

[evisu]

yes !! It works Thanks you so much, this will make my work so much easier. Absolutely awesome

[skate1991]

Wouldn't the quickest way to do this be,

Copy and paste row A with the 1000 customers names into a different tab, E.g Sheet 2
then Instert a coloum next to coloum B, Use a Vlookup, =VLOOKUP(B1,Sheet2!,A1:A1000,True,False) On the A1:A1000 you wil need to highlight them both and click F4, Then all you do is drag all the way to the bottom

Then Filter Largest to smallest all the people with N/A are not on the list and can be deleted and the people you are left with will be on both list,

And then If you need all 3 coloums in one spreadsheet open up a new one and copy using Right click paste speacial options then values has the picture 1,2,3 hope this is what you were looking for

Thanks