Drop Down Menu to display text from specific cells.

Copper Contributor



I'm a teacher, and I'm trying to create a marking assessment sheet, in which if I select a Level from a drop down menu, it will display the relevant information.

I've looked at lots of functions, but have really struggled to find anything that works.


Basically, my mark scheme has 3 columns (grids) and within each column, there are 5 levels, which each have different information. What I want to happen is that as soon as a user selects the level from a grid, it displays the information from the mark scheme in the cell underneath (see picture underneath, I couldn't fit all of the data in the print screen). I can't use IF, as there are more than two levels and I've tried using VLOOKUP to no avail. Can anyone help? The file is attached.






2 Replies

@Chris_Cook One possibility...


Grid 1: =VLOOKUP(B4,GRID,2,0)

Grid 2: =VLOOKUP(E4,GRID,3,0)

Grid 3: =VLOOKUP(H4,GRID,4,0)


Where GRID is defined in Name Manager as:




Also, if there was a need to make the col_index_num argument dynamic, based on the GRID #, you could use the MATCH function as follows:




Where HEAD is defined in Name Manager as:



The formula can then be copied across for Grid 2 and Grid 3.


See attached...