Formula to pull information from one column to another based on value in a third column.

[EWolfe]

Please see attached excel spreadsheet for example.

In the 'Names' tab there is a list of Names, files, and locations.
In the 'Locations' tab I would like to sort the information from the 'Names' tab by location.

For example; On the Locations tab, in line 2 I would like 'Ember' to appear automatically under the England and France columns and the Files column next to each of those should also pull the value from the corresponding Files column.

I believe it should be something like...
Y2: ...if text in column C or D = England then show text from column A
and
Z2: ...if text in column C or D = England then show text from Column B

Thank you for your help,

[oeldere]

E2 =IF(OR($C2=E$1,$D2=E$1),$A2,"") and drag down

F2 =IF(OR($C2=E$1,$D2=E$1),$B2,"") and drag down

[Bo_Ry]

Please try at
B2:EP21
=IF(COUNTIFS($C2:$D2,INDEX($E$1:$BP$1,CEILING(COLUMNS($E2:E2),2)-1)),IF(MOD(COLUMNS($E2:E2),2),$A2,$B2),"")

[EWolfe]

Thank you very much, this worked

[EWolfe]

Thank you very much!

[oeldere]

Glad I could help.

Thanks for marking the question solved.

You can add reputation by clicking on the star * add reputation.