Поиск в нескольких таблицах Excel, можете ли вы найти более элегантное решение?

Question

Это отчасти ради шутки, и я надеюсь, что оно не будет закрыто по соображениям субъективности, потому что, хотя "какое решение является наиболее элегантным", субъективно, "текущее решение является запутанным и уродливым", и его все видят.

Во всяком случае, у меня есть список в sheet1, например, так:

*Thing*            *type of thing*
Apple
Orange
Larceny
Cake
Banana
Murder

и на листе 2 вот так:

              fruit    confectionary      crime
Apple           x                                
Orange          x                                
Larceny                                     x    
Cake                         x                   
Banana          x                                
Murder                                      x   

и я хочу, чтобы вернуть столбец B в листе 1, как это

*Thing*               *type of thing*                                   
Apple                 fruit                        
Orange                fruit                        
Larceny               crime                        
Cake                  confectinary               
Banana                fruit                        
Murder                crime                        

Я опубликую свое текущее решение в качестве ответа. Это работает, я дам это, что ...

Как бы вы подошли к этой проблеме?

Answer 1

Вот мое решение. Index+match может использоваться для поиска значений в таблице в обоих направлениях. Это настраивает нормальное решение, чтобы найти х и вернуть заголовок столбца.

=INDEX($E$1:$G$1,MATCH("x",OFFSET($E$1:$G$1,MATCH(A2,$D$2:$D$7,0),),0))

Я положил все на один лист, чтобы его было легче увидеть, а также сделал функцию немного короче.

Согласно комментарию Барри ниже, лучшим вариантом было бы избежать смещения с помощью другой индексной функции, чтобы оно не было изменчивым.

=INDEX($E$1:$G$1,MATCH("x",INDEX($E$2:$G$7,MATCH(A2,$D$2:$D$7,0),0),0))

Answer 2

Вот мое предпочтительное решение, похожее на концепцию gtwebb (и предполагающее, что ваши данные выровнены, как на его скриншоте), но оно не использует OFFSET (который, я считаю, следует использовать только тогда, когда нет других вариантов, потому что он изменчив).

=INDEX($E$1:$G$1,MATCH("x",$E2:$G2,0))

Answer 3

Для моего текущего решения у меня есть

=MATCH(A1,Sheet1!A1:A10,0)

в столбце B, возвращая строку, содержащую вещь в sheet2. Все идет нормально. Но чтобы затем вернуть позицию x в этой строке на листе 2 и дать правильное слово, у меня есть это чудовище, использующее сцепленную строку R1C1, ссылающуюся внутри косвенной функции, чтобы преобразовать номер строки в ссылку, которую я действительно могу использовать :

=IF(INDIRECT("sheet2!R"&B1&"C2",0)="X","fruit",IF(INDIRECT("sheet2!R"&B1&"C3",0)="X","confectionary",IF(INDIRECT("sheet2!R"&B1&"C4",0)="X","crime","ERROR")))

Строка затем оценивается как «sheet2R [rownumber] C [column]», который передает косвенный (преобразует в обычную ссылку), который затем переходит к оператору if, чтобы скрыть присутствие x в соответствующем слове.