Excel Help

[pepp77]

I have simplified the information for this but basically I have a spreadsheet with the following in

Sheet 1

Code Pick Name
1,X,Andy
1,,Bob
1,,Colin
1,,Dave
2,,Edward
2,X,Frank
1,,George
2,,Harry
3,X,Ian
3,,James
3,,Kevin

Entered as csv
There are dozens of lines of codes (1 through 5) each line with a different name. So code 1 has about 15 names, code 2 has about 10, code 3 about 12 etc etc.

Each week I would like to go down the list and insert an X into column B to pick the person for each code.

Sheet 2

Code 1 = Name
Code 2 = Name
Code 3 = Name

I would like sheet 2 to autofil the Name for each code by querying sheet 1. So it would have to look down column A for a 1 for code 1, then check column
B for an X, if there is an X, put the name in column C in the cell in sheet 2. If not check the next row. Rinse repeat for all 5 codes until I have all 5 Codes autofilled in sheet 2

Hope this makes sense and any help appreciated.

Thanks

 

[Grrrrr]

Screenshot:

Formula (this is an array formula so you need to enter it by pressing Ctrl+Shift+Enter):

=VLOOKUP($E2&"X",CHOOSE({1,2},A:A&B:B,C:C),2,0)

Technique from:
http://chandoo.org/wp/2014/10/28/multi-condition-vlookup/

I'm sure there are a dozen ways to do this but this was the first that came to mind

 

[pepp77]

Sorry for the slow reply. Looks good will give it a go.

Thanks

 

[pepp77]

Little bit of fiddling to make it work across sheets, but it now does.

Thanks again.