Formula help (2-way lookup?) in Excel 2010

[Mandyanne04]

Hello!

Let me start by saying that compared to most of the people on this forum I am NOT very skilled at Excel. That being said, I know the basics, and learn quickly, so wanted to attempt a formula to help me with a database/spreadsheet issue, but am completely stuck. I have been watching hours of youtube videos and reading these boards and while I've gotten close, nothing has worked 100% accurately.

I am scheduling about 100 volunteers for different tasks during a work event.

On one sheet I have the tasks in the first column,like:
Monitor,
Tabling
Moving Assistant

and the time slots in the first row (1 per cell), like:
7am 8am 9am

I want to put in the volunteers where/when they will work.

Then, on the next sheet I want to have the VOLUNTEERS in the first column, like:

Joe
Jane
Bob

and the time slots in the first row, (like above) and have the spreadsheet automatically (with a formula) update what they are doing when.


Any help you can provide is welcome!
Thank you!!

 

[cyrilbrd]

Hi Mandyanne04,

Assuming that you do have the first part of the sheet that is the repartition of the people per task per hour:

	6:00	7:00	8:00	9:00	10:00	11:00	12:00	13:00	14:00	15:00	16:00	17:00	18:00	19:00	20:00
Monitor	Phil	Mark	John	Mark	Bill						Steve				
Tabling	Mark	Mark		June					Phil		Mark				
Moving	Steve	Phil	Steve			Phil	John	Bill			Phil				
Buzzing			Phil	John		Steve				John	Bill				
Slashing					John					Steve	John			Bill	
Exceling		John									Mark

This is placed in A1.

Now in Let's say A16 of the same sheet (it could be elsewhere, different sheet, different workbook...):

names	6:00	7:00	8:00	9:00	10:00	11:00	12:00	13:00	14:00	15:00	16:00	17:00	18:00	19:00	20:00
John		Exceling	Monitor	Buzzing	Slashing		Moving			Buzzing	Slashing				
Phil	Monitor	Moving	Buzzing			Moving			Tabling		Moving				
Bill					Monitor			Moving			Buzzing			Slashing	
Mark	Tabling	Monitor		Monitor							Tabling				
June				Tabling											
Steve	Moving		Moving			Buzzing				Slashing	Monitor

That is In A17 to A22 the names of the people (John, Phil, Bill, Mark, June and Steve)
in row 16 from Column B to Column P the same time as shown in the first table (B1 to P1)
and in B17:

=IF(ISNA(INDEX($A$2:$A$7,MATCH($A17,B$2:B$7,0))),"",INDEX($A$2:$A$7,MATCH($A17,B$2:B$7,0)))

Copy / paste to all other cells till P22.

Is this close to what you wanted?

 

[J.Ty.]

Hi,

As far as I understand you, below is what you enter manually, above is what you want to have computed automatically. The formula from B2 should be copied to fill the rest of the table.

Best,

J.Ty.
Beware: My Excel uses ";" in place of ",". Exchange it before using formulas.

Excel Workbook
	A	B	C	D
1		8am	9am	10am
2	Joe	Monitor	Tabling	Moving A.
3	Jane		Monitor	Tabling
4	Bob	Moving A.	Moving A.	Monitor
5	Helen	Tabling
6
7
8		8am	9am	10am
9	Monitor	Joe	Jane	Bob
10	Tabling	Helen	Joe	Jane
11	Moving A.	Bob	Bob	Joe
Sheet2

 

[Mandyanne04]

Thank you so very much cyrilbrd! The formula is perfect and will save me so much time - I really appreciate it!

 

[Mandyanne04]

Thank you J.Ty! I appreciate having a different formula option!

 

[cyrilbrd]

Glad it worked.

For your info J.Ty.'s formula is the same formula, however he used IFERROR and it is very good although it may not work with versions of excel prior to 2007. This will work with #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!.

The one I suggested only works for #N/A.

Cheers!.