Database Lookup both Horizontally & Verically (XP)

Topic

New Reply

I’m trying to create a report where I need the results in a cell to be the combination of looking up an ID number (ID numbers in my report are listed vertically in column A) and an assignment code (listed horizontally in row 5). I want the formula to look in the database (another Excel sheet in database format) and give me the mark for the that belongs to the particular ID for the particular assignment.

How can I do this?

Thanks in advance

Christa

Reply | Quote

Replies

Try using the Index function with two Match functions to supply the row and column parameters, Thusly

Reply | Quote

Thanks…that works for a simple database. Mine is a little more complicated (I should have been more clear in my original post). Please see the attached file. This is just a small piece of my database (actual DB has 2773 rows). The database is the last sheet in the workbook. The report I want to get is the first sheet. What I would like is for the fromula to be able to look up the ID in column A of the report and the Assignment in row 5 (columns D onward) of the report, match it to the corresponding ID and Assignment in the database and there by pull out the corresponding values in the Grade_Text column.

Thanks again in advance,

Christa

Reply | Quote

This can be done easily with a PIVOT Table
Select data
Data – pivot tablel report
ID, SLP_GRP, LAST_FIRST as row
REGISTRATION_ITEM as column
Sum of GRADE_TEXT as Data

Remove grand totals and subtotals
Format as desired

Steve

Reply | Quote

Thanks, Steve…

I tried that and it comes out in the format I would like…however all the Grades come out as zeros… I’ve tried changing all the options I can see but can’t seem to get it to work…any ideas?

Thanks,

Christa

Reply | Quote

Your Grade_texts are entered as text. Probably due to an import.

Highlight the column of data
Data – text to columns
Make sure delimited is marked
Press

Your text that looks like numbers are now numbers
Steve

Reply | Quote

Thank you! You’ve been a great help!

Reply | Quote