Copy multiple fields based on criteria

[JonSnow]

Hi all!

I need help populating a number of fields from another sheet, based on one criteria.

The dataset is large and keeps changing, so doing it once manually is not a solution I'm afraid.

See attached file for a simplified explanation.

thanks!

[BadlySpelledBuoy]

You could do this with a long an complicated formula, or you could use a helper column (Red text in the attached) and a simple INDEX/MATCH.

The results are the same as in your example workbook, but in a slightly different order.
Was there a reason they were ordered as such in yours?

BSB

[leelnich]

Here's a formula approach (no helper column). Paste in J5 and copy across+down:
=IFERROR(LOOKUP(1,0/($D$5:$D$17=$G5)/(COUNTIF($I5:I5,$C$5:$C$17)=0),$C$5:$C$17),"")

As in post#2, content is the same, but order of output is different from example.

Please click the Add Reputation star below any helpful posts, and use Thread Tools (up top) to mark your thread as SOLVED once you have your answer. Thanks!-Lee

[samba_ravi]

j5=IFERROR(INDEX($C:$C,SMALL(INDEX(($D$5:$D$17<>$G5)*10^10+ROW($D$5:$D$17),0),COLUMNS($J5:J5))),"")
Try this and copy across

[leelnich]

Just for fun, here's an ARRAY FORMULA* which produces alphabetized lists for each row! Paste in J5, copy across and down:

Please Login or Register  to view this content.

NOTE- The COLUMNS($J5:J5) clause points to the cell where you paste the formula. If you alter it, be sure to get the $ dollar sign right.

*Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.

Please click the Add Reputation star below any helpful posts, and use Thread Tools (up top) to mark your thread as SOLVED once you have your answer. Thanks!-Lee

[JonSnow]

Cheers guys, they worked perfectly!

Edit: How do I mark this as solved..?
Edit: Found it!