Как предотвратить разрыв VLOOKUP после добавления столбца в именованный диапазон?

Лично я предпочитаю использовать INDEX-MATCHкомбо для поиска вместо VLOOKUP. Вот пример из Pokedex, который я собирал для своей племянницы.

Чтобы получить скрытую способность Squirtle, я бы использовал эту формулу:

=INDEX(F2:F11,MATCH("Squirtle", A2:A11,0),1) 

Это дает тот же результат, что и:

=INDEX(A1:G11,MATCH("Squirtle",A1:A11,0),MATCH("Hidden Ability",A1:G1,0)) 

Хорошая вещь в INDEX-MATCH заключается в том, что в большинстве случаев вам не нужно ссылаться на весь диапазон данных, поэтому первая формула должна работать, даже если вы регулярно добавляете столбцы и строки в свой диапазон данных. Он также имеет еще одно преимущество: поскольку вы ссылаетесь только на 2 одномерных диапазона, он вычисляется быстрее.

Тем не менее, вы можете использовать MATCHс VLOOKUPформулами, чтобы получить номер столбца.

=VLOOKUP(C1,NAMED_RANGE,MATCH("COLUMN_TEXT",$A$1:$H1,0),0) 

где:
$A$1:$H$1первая строка в вашем диапазоне данных /
NAMED_RANGEимен, или строка, содержащая текст заголовка, это имя вашего диапазона данных,
"COLUMN_TEXT"текст заголовка для столбца, который содержит данные, которые вам нужны,
C1содержит значение поиска

Редактировать: Добавлен пример соответствия индекса по запросу Дуга. : D

Вы можете использовать MATCHфункцию:

Этот сайт имеет довольно хорошее объяснение того, как его использовать:
http://www.techonthenet.com/excel/formulas/match.php

Альтернативой является использование COLUMNфункции для считывания фактического номера столбца (если ваша таблица начинается не с столбца А, вам необходимо вычесть номер первого столбца в формуле). Итак, пример:

 =VLOOKUP(B2,$B$2:$D$300,COLUMN($D$1)-Column($B$1), False) 

Где столбец D содержит значение поиска

Альтернативным решением является создание пользовательской функции, которая должна сделать процесс очень простым.

Вот один подход, который создает функцию с именем XLOOKUP:

Public Function XLOOKUP(ByRef row As Variant, ByRef column As Variant, ByRef table_array As Variant) As Variant XLOOKUP = CVErr(xlErrNA) Dim xRow As Long, xCol As Long With table_array For xRow = 1 To .Rows.Count If .Cells(xRow, 1).Value = row Then For xCol = 1 To .columns.Count If .Cells(1, xCol).Value = column Then XLOOKUP = .Cells(xRow, xCol).Value Exit Function End If Next Exit Function End If Next End With  End Function 

Как только вы добавите его в свой проект, вы можете использовать его так:

=XLOOKUP(RowLabel, ColumnLabel, Range)

Вы также можете просто установить исходный vlookup, используя функцию COLUMN () для столбца со значением, которое нужно вернуть. В простой версии столбец значений поиска вашей таблицы находится в столбце A, а возвращаемое значение - в любом другом столбце, говорят в столбце L. Тогда используйте:

КОЛОННЫ (L: L)

для значения возвращаемого столбца. Сложнее, так как в столбце поиска нет столбца A? Просто вычтите из вышеперечисленного: (для поиска в столбце C)

КОЛОННЫ (L: L) -2

Значение здесь в том, что добавление и удаление столбцов не нарушает вашу формулу, поскольку Excel автоматически исправляет ее. Добавить столбец? Становится КОЛОННА (М: М). И так для удаления столбца. Все обрабатывается в Excel. И это действительно легко сделать. Колонны типа "WYO"? Excel не волнует. Найдите номер столбца вашего поискового столбца с помощью быстрой формулы COLUMN () в этом столбце, затем у вас есть значение для вычитания. Легко.

Без сомнения, менее мощный, чем Index / Match, и практика делает его идеальным, так что это сдерживает вас. Но это быстрый результат, который длится. И вы можете быстро научить этому других, пока вы оттачиваете свои навыки индекса / матча. Это также не так интуитивно, как использование именованных диапазонов, но часто вы не можете (Таблицы кто-нибудь?). (И это может быть базовое значение для именованного диапазона, в этом отношении.)

Так что, если вы хотите быстро, просто, трудно сломать с простыми изменениями столбцов (он сохранит свою ссылку, если вырезать и повторно вставить else-столбец, а не относительно взгляда с новой позиции, но для такого рода работы, я думаю, что это определенный плюс), то это решение думать.

Если вы укажете диапазоны столбцов в своей формуле поиска, то при добавлении или удалении столбцов формулы будут работать нормально.