Copy data based on output in a data validation list

I am looking to copy data to a master list on the same worksheet from a database of names based on the value of a certain cell.

Please see the attached spreadsheet to follow along.

Cell C2 contains a list (created from Data Validation) based on the column headers in cells E2:I2.
I would like this sheet to copy data to cell B5 and below based on the list number that has been selected in cell C2.
For example, if "List 1" is selected, I should see "John" in cell B5, "David" in cell B6 and so on.

I left cell I2 as "Manual" in the list in cell B2 as I would like all the names in B5 and below to be cleared so that the user could enter any names manually.

I've tried my best at doing this by using many IF statements in the macro, but to no avail whatsoever. I'm also sure there is a way to do this using the VBA equivalent of index/match, but I don't have the expertise to do it.

Any help is immensely appreciated!

Just select the list!! (after enabling the macros)

Hi rcm,

Many thanks for the quick response.

All looks good except for two issues:
- When "Manual" is selected in cell C1, I would like all the names in the list in cells B5 and below to be deleted so that the user can input any names he/she would like to.
- Assuming the lists are different lengths, I would like ONLY what shows up in the lists to show up in cell B5 and below. In your sheet, for example, if I first select "List 1" and then select "List 2" the additional two names (zz & aaa) still show up as the last two names, even though they aren't in the list.

Thanks in advance.

Sorry, its a cleanup bug, I´ll fix it

Now it clears column B before displaying the selected lists!!

rcm you've been immensely helpful!

At first I didn't completely understand the macro, but after a bit of tinkering I've managed to understand each line.

For anyone who faces a similar problem in the future, I've attached is the same file that rcm previously sent, except I have added comments to each line.
Hopefully any other macro novice like me can understand how rcm has solved my problem.

Many thanks!

you are welcome!! please mark the post as solved