How to append multiple resulted values into a column?

[Excel_Kid]

How to append multiple resulted values into a column?

Case:
Data in colA of worksheet1 should be compared with data in colB of worksheet2 and if matches retrieve value from colC of worksheet2 and paste result/value in colB of worksheet1. And most importantly for multiple results/values found how to append those values/results to colB of worksheet1?

[Excel_Kid]

Any help using vlookup, index, match, count etc functions?

[NBVC]

Try:

Please Login or Register  to view this content.

adjust ranges to suit your Sheet2 ranges... and then confirmed with CTRL+SHIFT+ENTER not just ENTER.

and copy down.

Note: You will get a blank if there are more repeats in Sheet1 than in Sheet 2 (this to avoid #NUM error)

If you are using 2007 then:

Try:

Please Login or Register  to view this content.

[Excel_Kid]

NBVC, Thank you for your response. Am lost, can you pls add an example with data?

[Excel_Kid]

=IFERROR(INDEX(Sheet2!$C$1:$C$5,SMALL(IF(Sheet2!$B$1:$B$5=A1,ROW(Sheet2!$B$1:$B$5)-ROW($A$1)+1),COUNTIF($A$1:A1,A1))),"")

Above formula is returning only ONE value to colB of worksheet1 and fails to find and thus append multiple values found in colC of worksheet2 into colB of worksheet1.

[NBVC]

See attached

[Excel_Kid]

Here is sample data:

worksheet 1:
colA colB
dp ?

worksheet 2
colB colC
dp 234
mj 334
dj 434
dp 534
ap 634
op 734
dp 834

Expected worksheet1 is,
colA colB
dp 234, 534, 834

[Excel_Kid]

can someone help?

[Excel_Kid]

Can anyone take a stab at my request?

[NBVC]

See attached for a couple of options....

If you truly want the results in single cells, then you would best download and install a free addin called Morefunc.xll.

then you would use this formula in B1:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and copied down.

If you are okay with have the results listed horizontally in separate cells, then you don't need the addin, you could use formula:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and copied down and then across how far you need to to ensure extraction of all matches.

a 3rd option is to use a VBA function.

Hit ALT+F11 and go to insert|Module, then in the VB editor paste this code:

Please Login or Register  to view this content.

Then apply formula similar to 1st one above:

Please Login or Register  to view this content.

and confirm with CTRL+SHIFT+ENTER not just ENTER

Note: If you make any changes to ranges, etc in the formula you have to reconfirm with CSE keys before copying down and/or across.