Dynamically reference table row depending on cell input

Question

I've found a couple of questions that touch on what I want to do, but not quite.

I have a table, say Table1

Segment	tot_bad_1at3	tot_bad_2at6	tot_bad_3at6	tot_bad_4at6
A	10	25	22	15
B	1	11	9	4
C	3	15	12	6
D	4	21	18	12
E	6	8	5	2

Then I have added calculated column to the table, called 'bad', in which I would like one of the existing columns selected based on a user input into Sheet2 cell A2 (called "bad_def"). This is a dropdown with the values: bad_1at3,bad_2at6,bad_3at6,bad_4at6

Hence the 'pseudo-formula' for table column 'bad' would be [@["tot_" & bad_def ]] or [@[indirect("tot_" & bad_def) ]]. In otherwords, I simple want the corresponding row in the table returned, depending on the user input. Another option, is to add a simple calculation to take the user input, and prefix it with "tot_" to create the already existing table column.

Currently, I am using =INDEX(Table1, ROW([@Segment])-1, MATCH("tot_" & bad_def, Table1[#Headers],0)), or with a series of nested if statements. Hence I am wondering if there is a more dynamic way to do this?

Appreciate the advice.

Good day all.

Answer 1

To create a dynamic calculated column for this scenario, use the XLOOKUP function (or the INDEX and MATCH functions) to find the lookup_value within the table header row, "Table1[#Headers]", and return the corresponding value from the current table row, "Table1[@]":

=XLOOKUP("tot_"&bad_def, Table1[#Headers], Table1[@])

OR:

=INDEX(Table1[@], MATCH("tot_"&bad_def, Table1[#Headers], 0))

NOTE: with the INDEX function, since the array parameter only contains a single row, the row_num parameter will accept the column_num argument.

Also, to avoid any circular reference warnings, do NOT include the column header of the newly created calculated column in the data validation list for "bad_def".