Show value from table based on selection

Copper Contributor

Hi all

 

On an Excel spreadsheet tab I have a table with the following data:

 

YearSession% Passed% FailedAverageHighest
2020July-2055454.88
2021July-2128725.05
2022July-2258424.26
2023July-2382184.06
2024July-2478224.27

 

On a separate tab I have the below table. On the data point column I have added a validation drop-down list of the above data points, i.e. % Passed, % Failed, Average and Highest. How can I then display that value in the data value column, taking into consideration the Year and Session? For example the first row below should show 58.

 

YearSessionData pointData value
2022July-22% Passed58
    
    

 

Many thanks!

 

2 Replies

@smc1906 

You can use XLOKUP() within XLOOKUP().

=XLOOKUP(C2,Table1[#Headers],XLOOKUP(A2,Table1[Year],Table1))


See also my enclosed sample document.

 

@smc1906 "taking into consideration the Year and Session", there are a number of methods that could work, depending on the functions available in your version of Excel. For example:

 

=SUMPRODUCT((Table1[Year] = A2) * (Table1[Session] = B2) * (Table1[#Headers] = C2) * Table1)

=INDEX(Table1, MATCH(1, (Table1[Year] = A2) * (Table1[Session] = B2), 0), MATCH(C2, Table1[#Headers], 0))

=SUMIFS(INDEX(Table1,, MATCH(C2, Table1[#Headers], 0)), Table1[Year], A2, Table1[Session], B2)

=SUMIFS(XLOOKUP(C2, Table1[#Headers], Table1), Table1[Year], A2, Table1[Session], B2)

=XLOOKUP(1, (Table1[Year] = A2) * (Table1[Session] = B2), XLOOKUP(C2, Table1[#Headers], Table1))

=FILTER(FILTER(Table1, (Table1[Year] = A2) * (Table1[Session] = B2)), Table1[#Headers] = C2)

 

Please see the attached sample workbook, if needed...