Compare columns and list a 3rd column with several possible values

[ertert00]

Hi,

I have an Excel sheet with 3 Columns A, B, C:

I want to check column A for values present (equal) in column C and list corresponding values from column B in Column D.

I tried the below code, but did not work.

=IF(ISNUMBER(MATCH(C1,A:A,0)),VLOOKUP(C1,A:B,2,0),"") from here http://superuser.com/questions/74978...a-third-column

I should note that in column B, there could be (but not necessary) several values corresponding to columns A and C. I have generated a mock-up excel sheet with Columns A,B,C as well as D, where I added the expected results.

[simarui]

i accomplished your expected results with the following vba code:

Please Login or Register  to view this content.

as in the attached

[ertert00]

Hi,

Thanks for the help. However, the code did not work correctly. It read only a few values from column B, and than the rest were 0s. I think Excel treated the values as 1 value (one large number) instead of many separated by commas.

[simarui]

i don't understand. it worked for me. can you upload your sample showing the error?

[Jacc]

Here is one solution with array formulas. On a very large dataset this will be slow.
There is also another solution with a UDF, it probably work better on large datasets since it's not doing unnecessary calculations.