EXCEL – индексирование значения на основе текстовой строки и числа в пределах диапазона

У меня проблемы с листом, над которым я работаю.

У меня есть два листа, список свойств и таблица поиска. Я пытаюсь вытащить значение из таблицы поиска, но это то место, где я попадаю в ловушку.

Мне нужно найти это значение на основе текстовой строки (типа свойства) и числа (общая площадь). Проблема, с которой я сталкиваюсь, заключается в том, что площадь пола точно не соответствует значениям в таблице поиска, поэтому я добавил два дополнительных столбца, указывающих нижний и верхний пределы диапазона. Но поскольку существует несколько типов свойств, существует по существу 6 небольших таблиц. Я разделил таблицу на различные названные диапазоны / косвенные, что несколько помогло, но я все еще не могу получить результаты, которые я получаю.

Lookup выглядит примерно так (ниже будет ссылка на экстракт с полной таблицей на dropbox (можете ли вы напрямую присоединить файлы?))

Dwelling Type / Dwelling Code / No. Bedrooms / Median Floor Area / Start Point / End Point MidTerrace House / MIDENDHOUSE / 1 / 53.7 / 0 / 60.65 MidTerrace House / MIDENDHOUSE / 2 / 67.6 / 60.65 / 76 Detatched House / DETHOUSE / 2- / 99.7 / 0 / 102.3 Detatched House / DETHOUSE / 3 / 104.9 / 102.3 / 125.35

Соответствующая часть списка свойств выглядит так: каждая строка представляет собой индивидуальный адрес ~ 5000.

 Total Floor Area / Dwelling type match / Dwelling Code /No. Bedrooms 74.54 / Semi-detached house / SEMIHOUSE / 47.5 / Mid-terrace house / MIDENDHOUSE /

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

Я пробовал использовать LOOKUP, VLOOKUP, INDEX, MATCH, INDIRECT, формулы массивов и всевозможные комбинации. Я чувствую, что ответ просто смотрит мне в лицо, но я просто вижу это. Я либо получаю ошибки, либо неправильные совпадения (в случае LOOKUP).

Ниже приведена ссылка на dropbox с выпиской из листа,
https://www.dropbox.com/s/nlv5z0ylm5lg3ym/Lookup%20Sheet%20Example.xlsx?dl=0

Листы являются податливыми, поэтому добавление столбцов не является проблемой, и я понимаю некоторые VBA, поэтому все решения оцениваются.

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

благодаря

Каллум

Я думаю, что это может быть решение, которое вы ищете.

Прежде всего, я просто преобразую ваши таблицы в структурированные таблицы (в основном выбираем созданные вами таблицы, а затем нажмите CONTROL-T (или под лентой INSERT, нажмите «Таблицы» и преобразуйте их в структурированные таблицы) ).

Я дал вашим таблицам то же имя, что и листы (PropertyList и LookupTable). Затем я добавил эту формулу в колонку «Кол-во спален»:

 =COUNTIFS(LookupTable[Dwelling Code],[Dwelling Code],LookupTable[Start Point],"<="&[Total Floor Area])+NUMBERVALUE(VLOOKUP([Dwelling Code],LookupTable[Dwelling Code]:LookupTable[No. of bedrooms],2,FALSE),"-")-1

Как это работает:

Мы хотим получить подсчет всех ячеек, которые соответствуют как [Коду проживания], и где [Начальная точка] (размер пола) меньше или равна [Общая площадь пола].
Но так как некоторые из этих Живущих кодов начинаются со счета в спальне «2 -», мы должны учитывать их, поэтому мы делаем Vlookup первой ячейки [Жилищного кода] и видим, что их начало [Нет. спальни], и добавьте это к нашей общей сумме.
Мы используем формулу «NUMBERVALUE» для извлечения 2 из текста, чтобы она не вызывала ошибки #VALUE.
Наконец, мы делаем коррекцию в конце с минусом (-1), потому что мы дважды подсчитываем первую ячейку каждого кода жилья, когда мы используем как VLOOKUP, так и COUNTIFS. (мы добавляем 1 или 2 каждый раз с помощью нашего vlookup, поэтому нам нужно вычесть 1).

Если по какой-то причине вы не хотите использовать структурированные таблицы, то эта очень запутанная формула будет делать то же самое, но вам нужно поместить ее в ячейку G2 в листе «Список свойств».

 =COUNTIFS('Lookup Table'!$C$3:$C$24,'Property List'!F2,'Lookup Table'!$F$3:$F$24,"<="&'Property List'!A2)+NUMBERVALUE(VLOOKUP('Property List'!F2,'Lookup Table'!$C$3:$C$24:'Lookup Table'!$D$3:$D$24,2,FALSE),"-")-1

Удалить смещение строки на 1

Как сделать отрицательные SumIfs в VBA с помощью Worksheet.Function

динамически подсчитывать количество столбцов в листе excel

Целевая ячейка Autofit Target, но только когда контент больше

не может игнорировать дату при расчете разницы во времени

Как получить правильное большинство 3 символов формулы, а не значение ячейки?

Копировать столбец даты

Необходимо оптимизировать мой код, который перемещает ячейки на основе строки

Формула сортировки в Excel

Можно ли вставить индексный матч?

Использование двух массивов для сортировки по таблицам