Lookup and/or Match

[KTT]

I have a workbook that I am using for scheduling staff. On the first worksheet is the dates (row), employee names broken up by department (column), etc. If a staff member is working then "W" is in the cell corresponding cell and if they are working and on call for that night then WC is in the appropriate cell.

The second worksheet is a schedule of who is on call for the week. Dates are listed in Column A and departments are listed in Row 1. I need to look up the person on call on worksheet one and enter that person's name on worksheet 2.

Here's the problem - HLOOKUP is not working because I cannot sort the columns in alphabetical order which is required by HLOOKUP. Any suggestions?

Thank you in advance for your guidance.

[ChemistB]

Sounds like you'll need to use INDEX and MATCH but I'm having trouble picturing your first spreadsheet.
Across the top is Dates
Down the left (Col A)is department names?
Col B is Employee names?
Beneath each date is either a W or WC or blank?

Thanks

ChemistB

[NBVC]

HLOOKUP, like VLOOKUP requires list in ascending order only if you don't include the 4th FALSE argument (or if you use TRUE as that argument).

So do your HLOOKUP and insert FALSE as a 4th argument.

Look at HLOOKUP help for detail on how to use the function.

[KTT]

Thanks for the reply. I did try HLOOKUP using the False argument and it returns "0" when it should give me a name. Any suggestions? I appreciate the guidance.

[NBVC]

Thanks for the reply. I did try HLOOKUP using the False argument and it returns "0" when it should give me a name. Any suggestions? I appreciate the guidance.

Then you either have a blank or you indexed the wrong row and it is seeing a blank...

The best bet is to post a sample of your sheet here (zipped Excel sheet) and indicate your expected results.

[KTT]

Yes, I do have blanks if the employee is not scheduled. Is that the problem?

[NBVC]

I'd have to see the spreadsheet...and what your formula looks like.

Please post the sample sheet.

[KTT]

Sounds like you'll need to use INDEX and MATCH but I'm having trouble picturing your first spreadsheet.
Across the top is Dates
Down the left (Col A)is department names?
Col B is Employee names?
Beneath each date is either a W or WC or blank?

Thanks

ChemistB

You have this correct except Col A is the employee names separated by department. I am setting the HLOOKUP to search only the employee names within one department.