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


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


You can use XLOKUP() within XLOOKUP().


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