Forum Discussion

smc1906's avatar
smc1906
Copper Contributor
Jun 21, 2024

Show value from table based on selection

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!

 

BI & Data Analysis
excel
Formulas and Functions
  • djclements's avatar
    djclements
    Bronze Contributor
    Jun 21, 2024

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

Resources