Forum Discussion

Chris_Cook's avatar
Chris_Cook
Copper Contributor
Jun 14, 2024

Drop Down Menu to display text from specific cells.

Hi.

 

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.

 

 

 

Excel
office 365
  • djclements's avatar
    djclements
    Bronze Contributor
    Jun 15, 2024

    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:

    =Sheet3!$A$2:$D$6

     

    EDIT:

    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:

     

    Grid 1: =VLOOKUP(B4,GRID,MATCH(B3,HEAD,0),0)

     

    Where HEAD is defined in Name Manager as:

    =Sheet3!$A$1:$D$1

     

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

     

    See attached...

     

Resources