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