The arguments of the table lookup are “$K$204, 1”, i.e. row position=$K$204 and column position=1, so the order is wrong. A single horizontal row range does not contain a row 3 and the like, so the correct rating cannot be extracted. In addition, the condition is “if ＞2 then B3”, which is the opposite in truth value from the requirement of extracting those 2 or lower (it returns the higher-rated ones). Doubly wrong.
×Incorrectイ
The order of the IF branches (if ＞2 then null, otherwise B3) results in “2 or lower → B3”, so the logic is consistent, but the table lookup arguments are “$K$204, 1”, with the row position and column position swapped. In a single horizontal row range the row position must be 1 and the item number must be placed in the column position, so the rating of the specified item cannot be extracted. Wrong.
○ウ
正解The table_lookup function is “table_lookup(cell range, row position, column position)”, and it returns the cell value at the specified row and column using the top-left of the range as the reference. Here the range $B3:$G3 is a single horizontal row consisting of only row 3 (the ratings of the 6 question items in columns B to G), so the row position is fixed at 1, and by giving $K$204 (the entered item number) as the column position, you can extract the single rating for that item. If the extracted rating is 2 or lower (≦2) the result is true, otherwise false. The question asks to “return the rating in cell B3 of the tally table if it is 2 or lower, otherwise a null value”, so B3 is placed in the true branch of IF and null in the false branch. Therefore 「IF(table_lookup($B3:$G3, 1, $K$204) ≦ 2, B3, null)」 is correct, and ウ is the answer. Because the columns use relative references rather than absolute references, copying from J3 to O202 correctly references the rating of each row and column.
×Incorrectエ
The order of the table lookup arguments “1, $K$204” is correct, but the IF branches are reversed. When the condition “≦2” is true it returns null and when false it returns B3, so the questionnaires “with a rating of 2 or lower” that you want to extract become null values, and conversely those with a rating of 3 or higher are left in. This is the opposite of the requirement and is wrong.
ここがポイント
The argument order of the table_lookup function is “table_lookup(cell range, row position, column position)”. In a single horizontal row range, fix the row position at 1 and specify the item number to be extracted (here $K$204) as the column position.
IF(condition, value if true, value if false) returns the second argument when the condition holds and the third argument when it does not. In line with the question “the rating if 2 or lower, otherwise a null value”, place B3 in the true branch and null in the false branch.