Formula using 2 points of reference to return a value from a table

[MrvinGover]

I am looking for help on a formula in a calendar, (see attached) I have
a table with the Date, Name and Location, and what I am looking to do
is have a formula in the calendar that looks at the date on the
calendar, and the name to the far most left and return the location
that person is in on that date.

I am not very familiar with "Index" or "match" functions, but I think
that is what is needed here.


+-------------------------------------------------------------------+
|Filename: Calendar.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4092 |
+-------------------------------------------------------------------+

 

[Guest]

The attachment doesn't come through. I could be wrong but I don't think they
allow attachments on this site.

You are correct in thinking that index, match, lookup combination(s) will be
your solution.

 

[MrvinGover]

Do you have any good direction to point me in which to learn more about
the Index and Match functions? The excel tutorial is a little weak.

 

[Biff]

Hi!

I'll "fix" your file.

Where do you want me to send it when I'm done?

Biff

 

[Dave Peterson]

You may want to read Debra Dalgleish's notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))

 

[MrvinGover]

Thanks Biff, but I think I understand it now, I had to fully understand
the Array feature with the Ctrl+Shift+Enter. But if you have it done
that would be great!

(e-mail address removed)

 

[Biff]

I haven't done it yet. I was waiting for a response. I'll do it this
evening.

One thing you need to consider is using an error trap in the formula. Since
you have many "holes" in the data the basic formula will return #N/A errors.
You probably don't want to see those all over the place.

Biff