Forum Discussion
smc1906
Jun 21, 2024Copper Contributor
Show value from table based on selection
Hi all
On an Excel spreadsheet tab I have a table with the following data:
Year | Session | % Passed | % Failed | Average | Highest |
2020 | July-20 | 55 | 45 | 4.8 | 8 |
2021 | July-21 | 28 | 72 | 5.0 | 5 |
2022 | July-22 | 58 | 42 | 4.2 | 6 |
2023 | July-23 | 82 | 18 | 4.0 | 6 |
2024 | July-24 | 78 | 22 | 4.2 | 7 |
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.
Year | Session | Data point | Data value |
2022 | July-22 | % Passed | 58 |
Many thanks!
- djclementsBronze Contributor
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...
- dscheikeyBronze Contributor
You can use XLOKUP() within XLOOKUP().
=XLOOKUP(C2,Table1[#Headers],XLOOKUP(A2,Table1[Year],Table1))
See also my enclosed sample document.