Index Function

[Carrie]

I am having trouble figuring out nested function to pull the correct
information without error. I have created a calendar in excel for various
time periods to show instructors booked for training classes. Across the top
row is a julian dates and down the first column in a name of an instructor. I
need an index/lookup to find the location of each class. Example below:

Julian Date
2454486

Instructor Location

Anthony O'Briant USA Town
Bart Decker Anytown, USA

The array is a separate worksheet with a list of several classes and
locations all of which can start on the same day. Because of this I think it
has to be an index to isolate the instructor(s) as well as the start class
date.

Julian
Begin
Class Date INSTR CLASS LOCATION
2454486 Bart Anytown, USA
2454486 Jim Anytown, USA
2454486 Justin Anytown, USA
2454486 Lee Anytown, USA
2454486 Dusty USA Town
2454486 Anthony USA Town
2454486 Tim USA Town
2454486 Geoff USA Town
2454491 Bart Town USA
2454491 Jim Town USA
2454491 Anthony Town USA
2454491 Tim Town USA

I have tried several variations of functions without success. Below is one
of the [non-index] variations of functions I have tried with a "#Value!"
error..........Can anyone help? [V4= Julian Date, A9=Instructors name]

=IF(AND(VLOOKUP(V4,'[TRAINING Location
Sheet.xls]Classes'!$A:$K,7,FALSE),VLOOKUP(A9,'[TRAINING Location
Sheet.xls]Classes'!$C:$K,5,FALSE)),'[TRAINING Location
Sheet.xls]Classes'!$G$2,"Need Class Information")

Thanks in advance
Carrie

 

[T. Valko]

Can't tell what you're trying to do with this.

If you want to return the town name by looking up the persons name for a
specific date try something like this:

=VLOOKUP(A10,A2:F6,MATCH(B10,A1:F1,0),0)

Where:

A10 = lookup persons name
A2:F6 = table of town names
B10 = lookup date
A1:F1 = dates