Drop Down Menu to display text from specific cells.

Copper Contributor

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.

 

Chris_Cook_0-1718404969526.png

Chris_Cook_1-1718405077847.png

 

 

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:

=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...

 

Spoiler
 

=XLOOKUP(B4,Sheet3!A2:A10,Sheet3!B2:B10)