Изтеглете примерната работна книга
Чувствителен към регистър VLOOKUP - Excel
Този урок ще покаже как да извършите чувствителен към регистър VLOOKUP в Excel, като използвате два различни метода.
Метод 1 - чувствителен към регистър VLOOKUP с помощна колона
= VLOOKUP (MAX (ТОЧНО (E2, $ B $ 2: $ B $ 7)*(ROW ($ B $ 2: $ B $ 7))), $ C $ 2: $ D $ 7,2,0)
Функция VLOOKUP
Функцията VLOOKUP се използва за търсене на приблизително или точно съвпадение за стойност в най -лявата колона на диапазон и връща съответната стойност от друга колона. По подразбиране VLOOKUP ще работи само за нечувствителни към регистъра стойности по следния начин:
Чувствителен към регистър VLOOKUP
Чрез комбиниране на VLOOKUP, EXACT, MAX и ROW, можем да създадем VLOOKUP чувствителна към регистъра формула, която връща съответната стойност за нашия VLOOKUP, чувствителен към регистър. Нека преминем през пример.
Имаме списък с артикули и съответните им цени (забележете, че идентификационният номер на елемента е чувствителен към малки и главни букви):
Да предположим, че сме помолени да получим цената за даден артикул, като използваме неговия идентификационен номер по следния начин:
За да постигнем това, първо трябва да създадем помощна колона, използвайки ROW:
= ROW () щракнете и плъзнете (или щракнете двукратно), за да запълните предварително всички редове в диапазона
След това комбинирайте VLOOKUP, MAX, EXACT и ROW във формула така:
= VLOOKUP (MAX (ТОЧНО (,)*(ROW ())), ,, 0)
= VLOOKUP (MAX (ТОЧНО (E2, $ B $ 2: $ B $ 7)*(ROW ($ B $ 2: $ B $ 7))), $ C $ 2: $ D $ 7,2,0)
Как действа формулата?
- Функцията EXACT проверява идентификационния номер на елемента в E2 (стойност за търсене) спрямо стойностите в B2: B7 (диапазон за търсене) и връща масив от TRUE, когато има точно съвпадение или FLASE в масив {FLASE, FLASE, FLASE, FLASE, FLASE, ИСТИНА}.
- След това този масив се умножава по ROW масива {2, 3, 4, 5, 6, 7} (имайте предвид, че това съвпада с нашата помощна колона).
- Функцията MAX връща максималната стойност от получения масив {0,0,0,0,0,7}, което е 7 в нашия пример.
- След това използваме резултата като стойност за търсене във VLOOKUP и избираме помощната колона като диапазон за търсене. В нашия пример формулата връща съответстващата стойност от $ 16.00.
Метод 2 - чувствителен към регистър VLOOKUP с „виртуална“ помощна колона
= VLOOKUP (MAX (ТОЧНО (D2, $ B $ 2: $ B $ 7)*(ROW ($ B $ 2: $ B $ 7))), ИЗБЕРИ ({1,2}, ROW ($ B $ 2: $ B $ 7) , $ C $ 2: $ C $ 7), 2,0)
Този метод използва същата логика като първия метод, но премахва необходимостта от създаване на помощна колона и вместо това използва CHOOSE и ROW за създаване на „виртуална“ помощна колона по следния начин:
= VLOOKUP (MAX (EXACT (,)*(ROW ())), ИЗБОР ({1,2}, ROW (),),, 0)
= VLOOKUP (MAX (ТОЧНО (D2, $ B $ 2: $ B $ 7)*(ROW ($ B $ 2: $ B $ 7))), ИЗБЕРИ ({1,2}, ROW ($ B $ 2: $ B $ 7) , $ C $ 2: $ C $ 7), 2,0)
Как действа формулата?
- Първата част на формулата работи по същия начин като първия метод.
- Комбинирането на CHOOSE и ROW връща масив с две колони, една за номера на ред и друга за цената. Масивът е разделен с точка и запетая, за да представлява следващия ред и запетая за следващата колона по следния начин: {2,45; 3,83; 4,23; 5,74; 6,4; 7,16}.
- След това можем да използваме резултата от първата част на формулата във VLOOKUP, за да намерим съответната стойност от нашия масив CHOOSE и ROW.
Чувствителен към регистър VLOOKUP в Google Таблици
Всички горни примери работят абсолютно същите в Google Sheets, както и в Excel.
