Referencing data from any cell within a table using multiple criteria

PriEOn

New Member
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 -->

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

RoryA

MrExcel MVP, Moderator
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.

Replies
4
Views
94
Replies
1
Views
49
Replies
3
Views
492
Replies
3
Views
59
Replies
1
Views
48

1,191,685
Messages
5,988,063
Members
440,125
Latest member
vincentchu2369

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back