Referencing data from any cell within a table using multiple criteria

[PriEOn]

Hi, hope someone can help.

I have a training schedule spreadsheet that I'm trying to report from:


Table A:

• On the X axis I have User 1, User 2, User 3, etc

• On the Y axis I have Date, AM/PM Session and Coursename

Note that the Y-axis lines are repeated several times with the same date but with different coursenames being run per day.

So I would like to report from this table into another table (on a different sheet) with the following format:


Table B:

• On the X axis I have 2 intances of the same date per date - the first instance represents AM and the second PM

• On the Y axis I have the total username list

What I want is to reference any cell in Table B (using username, date and AM/PM) to interrogate the same values in Table A to get the coursename (held in the second column).

Quite tricky to describe this - hope I haven't lost you! Unfortunately there doesn't seem to be a way that I can attach the spreadsheet so you'll have to make do with my explanation.

I've tried VLOOKUP but it only references from a single column as opposed to a whole table.

Any ideas? (or Queries?)

Priyesh
<!-- / message --><!-- attachments -->

 

[RoryA]

The easiest way would be to add a column to the start of table A that concatenates the three Y axis criteria into one cell. You can then use a MATCH formula on that to get the required row number.