Match Columns & If True Copy Row - How?

[thussain]

Hi, I have a list with multiple unique numbers and then a list with a single corresponding unique number but next to the single entries are the surnames and forenames. I want to copy them over to the mulitple entry fields. For example:

Enrol Ref No Surname First Forename
0305526 0305526 ISMAIL AWRENG
0305526 0503253 ZAHID SOHAIL
0305526 0509352 HOLTEN THOMAS
0305526 0608249 BROWN REBECCA
0305526 0702933 BELLWOOD PAIGE
0305526 0703808 CARROLL GEORGINA
0305526 0704205 GARTERY JORDAN
0374016 0708721 HAHAA KAREM SARAH QADER
0374770 0708907 SHARIF KARWAN
0503253 0708916 MOHAMMED TALAR
0503253 0800413 BROOKS LEONA
0503253 0800420 BEAUMONT MICHAEL
0503253 0800501 CLAYTON JASMIN
0503253 0802033 HALLSWORTH KRISTIAN


A = Enrol
B = Blank
C = Blank
D = Ref No
E = Surname
F = Forename

I want A to match D and where it does match D I want it to copy the row for E and F next to it on to B and C. In other words in the example above (and attached) A2 to A8 matches D2, how do I then get it to copy E2 and F2 over to B2-B8 and C2-C8.

How do I do that? I'll attach a small sample as the actual one runs in the thousands.

Sample.xlsx

[Fotis1991]

Perhaps in B2. Copy down and across to C2 this ARRAY formula.

=IFERROR(INDEX(E$2:E$15,SMALL(IF($D$2:$D$15=$A2,ROW(E$2:E$15)-1),ROW(E$1))),"no match")

[thussain]

Thanks Fotis but that doesn't work. The first entry repeats itself seven times therefore I need a formula which will pick that up and copy the name over seven times because it matches seven times. The formulas you gave seems to just copy them over in order rather than match and then copy.

In other words A2 all the way to A8 has the same enrol number of 0305526. I want the formula to see that this number exists in D2 therefore I want it to copy what is next to D2 i.e. E2 and F2 in to what is next to A2-A8 i.e. B2-B8 and C2-C8. Am I making sense?

A2-A15 and B2-B15 and C2-C15 should look like this:

0305526 ISMAIL AWRENG
0305526 ISMAIL AWRENG
0305526 ISMAIL AWRENG
0305526 ISMAIL AWRENG
0305526 ISMAIL AWRENG
0305526 ISMAIL AWRENG
0305526 ISMAIL AWRENG
0374016 No Match No Match
0374770 No Match No Match
0503253 ZAHID SOHAIL
0503253 ZAHID SOHAIL
0503253 ZAHID SOHAIL
0503253 ZAHID SOHAIL
0503253 ZAHID SOHAIL

[Fotis1991]

What is this that i am missing?

Formula does what you describe.

[popipipo]

Why not this simple (not array) formula?

Please Login or Register  to view this content.

[thussain]

You're right... I think I copied it wrong by just dragging it down. Thanks! +Rep

[Fotis1991]

Why not this simple (not array) formula?

Please Login or Register  to view this content.

Correct! No reason. Regular formula.

=IFERROR(INDEX(E$2:E$15,MATCH($A2,$D$2:$D$15;0)),"no match")

[Fotis1991]

You're right... I think I copied it wrong by just dragging it down. Thanks! +Rep

You just didn't confirm the formula as ARRAY. See my signature.

But in any case you don't need the ARRAY formula. See posts #5 and #7!

[thussain]

^ it says the formula in #5 and #7 has an error? But I'm using you array anyway, it does the job, thanks!

[Fotis1991]

............................