Преминаване към основното съдържание

Търсене на стойности с VLOOKUP, INDEX или MATCH

Съвет: Опитайте да използвате новите функции XLOOKUP и XMATCH , подобрени версии на функциите, описани в тази статия. Тези нови функции работят в произволна посока и връщат точно съвпадение по подразбиране, което ги улеснява и е по-удобно да се използват от предшествениците си.

Да предположим, че имате списък с номерата на местоположенията на Office и трябва да знаете кои служители има във всеки отделен Office. Електронната таблица е огромна, така че може би си мислите, че е предизвикателна задача. Всъщност е доста лесно да се прави с функция LOOKUP.

Функциите VLOOKUP и HLOOKUP , заедно с индекс и Match,са някои от най-полезните функции в Excel.

Забележка: Функцията "Съветник за справки" вече не е налична в Excel.

Ето един пример за това как да използвате VLOOKUP.

=VLOOKUP(B2;C2:E7;3;TRUE)

В този пример B2 е първият аргумент– елемент от данните, които функцията трябва да работи. За VLOOKUP този първи аргумент е стойността, която искате да намерите. Този аргумент може да бъде препратка към клетка или фиксирана стойност, като например "Smith" или 21 000. Вторият аргумент е диапазонът от клетки, C2: E7, в който да се търси стойността, която искате да намерите. Третият аргумент е колоната в диапазона от клетки, която съдържа стойността, която търсите.

Четвъртият аргумент е по желание. Въведете TRUE или FALSE. Ако въведете TRUE или оставите аргумента празен, функцията връща приблизително съвпадение на стойността, която задавате в първия аргумент. Ако въведете FALSE, функцията ще съвпада със стойността, предоставена от първия аргумент. С други думи Оставянето на четвъртия аргумент празен – или въвеждането на TRUE – ви дава по-голяма гъвкавост.

Този пример ви показва как работи функцията. Когато въведете стойност в клетка B2 (първия аргумент), VLOOKUP търси в клетките в диапазона C2: E7 (2ри аргумент) и връща най-близкото съвпадение от третата колона в диапазона, колона E (трети аргумент).

Типично използване на функцията VLOOKUP

Четвъртият аргумент е празен, така че функцията връща приблизително съвпадение. Ако няма, трябва да въведете една от стойностите в колоните C или D, за да получите някакъв резултат.

Когато сте спокойни с VLOOKUP, функцията HLOOKUP е също толкова лесна за използване. Въвеждате един и същ аргумент, но той търси в редове вместо колони.

Използване на INDEX и MATCH вместо VLOOKUP

Има някои ограничения при използването на VLOOKUP – функцията VLOOKUP може само да търси стойност отляво надясно. Това означава, че колоната, съдържаща стойността, която търсите, винаги трябва да се намира отляво на колоната, съдържаща върнатата стойност. Сега, ако електронната ви таблица не е създадена по този начин, не Използвайте VLOOKUP. Вместо това използвайте комбинация от функциите INDEX и MATCH.

Този пример показва малък списък, в който стойността, която искаме да намерим – Чикаго – не се съдържа в най-лявата колона. Така че не можем да използваме VLOOKUP. Вместо това ще използваме функцията MATCH, за да намерим Чикаго в диапазона B1:B11. Стойността се намира в ред 4. След това функцията INDEX ще използва тази стойност като аргумент за търсене и ще намери населението на Чикаго в 4-тата колона (колона D). Използваната формула е показана в клетка A14.

Използване на INDEX и MATCH за търсене на стойност

За повече примери за използването на индекс и MATCH вместо на VLOOKUP, вижте статията HTTPS://www.mrexcel.com/Excel-tips/Excel-VLOOKUP-index-Match/ от Bill Желен, Microsoft MVP.

Опитайте

Ако искате да експериментирате със справочни функции, преди да ги изпробвате с ваши собствени данни, Ето някои примерни данни.

Пример за VLOOKUP на работа

Копирайте следните данни в празна електронна таблица.

Съвет: Преди да поставите данните в Excel, задайте ширина от 250 пиксела на колоните от A до C и след това щракнете върху Текст на повече редове (раздел Начало, група Подравняване).

Плътност

Вискозитет

Температура

0,457

3,55

500

0,525

3,25

400

0,606

2,93

300

0,675

2,75

250

0,746

2,57

200

0,835

2,38

150

0,946

2,17

100

1,09

1,95

50

1,29

1,71

0

Формула

Описание

Резултат

=VLOOKUP(1;A2:C10;2)

Използвайки приблизително съвпадение, търси стойност 1 в колона A, намира най-голямата стойност, по-малка или равна на 1, в колона A, която е 0,946, и връща стойността от колона B в същия ред.

2,17

=VLOOKUP(1;A2:C10;3;TRUE)

Използвайки приблизително съвпадение, търси за стойност 1 в колона A, намира най-голямата стойност, по-малка или равна на 1, в колона A, която е 0,946, и връща стойността от колона C в същия ред.

100

=VLOOKUP(0,7;A2:C10;3;FALSE)

Използвайки точно съвпадение, търси стойността 0,7 в колона A. Тъй като там няма точно съвпадение, връща се грешка.

#N/A

=VLOOKUP(0,1;A2:C10;2;TRUE)

Използвайки приблизително съвпадение, търси стойността 0,1 в колона A. Тъй като стойността е по-малка от най-малката в колона А, се връща грешка.

#N/A

=VLOOKUP(2;A2:C10;2;TRUE)

Използвайки приблизително съвпадение, търси стойността 2 в колона A, намира най-голямата стойност, по-малка или равна на 2, в колона A, която е 1,29, и връща стойността от колона B в същия ред.

1,71

Пример за HLOOKUP

Копирайте всички клетки в тази таблица и ги поставете в клетка A1 на празен работен лист в Excel.

Съвет: Преди да поставите данните в Excel, задайте ширина от 250 пиксела на колоните от A до C и след това щракнете върху Текст на повече редове (раздел Начало, група Подравняване).

Мостове

Лагери

Болтове

4

4

9

5

7

10

6

8

11

Формула

Описание

Резултат

=HLOOKUP("Мостове"; A1:C4; 2; TRUE)

Търси "Мостове" в ред 1 и връща стойността от ред 2, която е в същата колона (колона A).

4

=HLOOKUP("Лагери"; A1:C4; 3; FALSE)

Търси "Лагери" в ред 1 и връща стойността от ред 3, която е в същата колона (колона B).

7

=HLOOKUP("B"; A1:C4; 3; TRUE)

Търси "B" в ред 1 и връща стойността от ред 3, която е в същата колона. Тъй като не е намерено точно съвпадение за "B", използва се най-голямата стойност в ред 1, която е по-малка от "B": "Мостове", в колона A.

5

=HLOOKUP("Болтове"; A1:C4; 4)

Търси "Болтове" в ред 1 и връща стойността от ред 4, която е в същата колона (колона C).

11

=HLOOKUP(3; {1\2\3;"a"\"b"\"c";"d"\"e"\"f"}; 2; TRUE)

Търси числото 3 в масив от константи с три реда и връща стойността от ред 2 в същата (в този случай третата) колона. В масива от константи има три реда със стойности, като всеки ред се разделя с точка и запетая (;). Тъй като "c" е намерено в ред 2 и в същата колона като 3, връща се "c".

c

Примери за индекс и СЪВПАДЕНИЕ

Този последен пример използва функциите INDEX и MATCH заедно, за да върне най-ранния номер на фактура и съответната му дата за всеки от петте града. Тъй като датата се връща като число, използваме функцията TEXT, за да я форматираме като дата. Функцията INDEX всъщност използва резултата от функцията MATCH като свой аргумент. Комбинацията от функциите INDEX и MATCH се използва два пъти във всяка формула – веднъж за връщане на номера на фактурата, а след това за връщане на датата.

Копирайте всички клетки в тази таблица и ги поставете в клетка A1 на празен работен лист в Excel.

Съвет: Преди да поставите данните в Excel, задайте ширина от 250 пиксела на колоните от A до D и след това щракнете върху Текст на повече редове (раздел Начало, група Подравняване).

Фактура

Град

Дата на фактурата:

Най-стара фактура по градове, с дата

3115

Атланта

4/7/12

="Атланта = "&INDEX($A$2:$C$33;MATCH("Атланта";$B$2:$B$33;0);1)& "; Дата на фактурата: " & TEXT(INDEX($A$2:$C$33;MATCH("Атланта";$B$2:$B$33;0);3);"m/d/yy")

3137

Атланта

4/9/12

="Остин = "&INDEX($A$2:$C$33;MATCH("Остин";$B$2:$B$33;0);1)& "; Дата на фактурата: " & TEXT(INDEX($A$2:$C$33;MATCH("Остин";$B$2:$B$33;0);3);"m/d/yy")

3154

Атланта

11.4.12

="Далас = "&INDEX($A$2:$C$33;MATCH("Далас";$B$2:$B$33;0);1)& "; Дата на фактурата: " & TEXT(INDEX($A$2:$C$33;MATCH("Далас";$B$2:$B$33;0);3);"m/d/yy")

3191

Атланта

21.4.12

="Ню Орлиънс = "&INDEX($A$2:$C$33;MATCH("Ню Орлиънс";$B$2:$B$33;0);1)& "; Дата на фактурата: " & TEXT(INDEX($A$2:$C$33;MATCH("Ню Орлиънс";$B$2:$B$33;0);3);"m/d/yy")

3293

Атланта

25.4.12

="Кричим = "&INDEX($A$2:$C$33;MATCH("Кричим";$B$2:$B$33;0);1)& "; Дата на фактурата: " & TEXT(INDEX($A$2:$C$33;MATCH("Кричим";$B$2:$B$33;0);3);"m/d/yy")

3331

Атланта

27.4.12

3350

Атланта

28.4.12

3390

Атланта

1.5.12

3441

Атланта

2.5.12

3517

Атланта

8.5.12

3124

Остин

9.4.12

3155

Остин

11.4.12

3177

Остин

19.4.12

3357

Остин

28.4.12

3492

Остин

5.6.12

3316

Далас

25.4.12

3346

Далас

28.4.12

3372

Далас

1.5.12

3414

Далас

1.5.12

3451

Далас

2.5.12

3467

Далас

2.5.12

3474

Далас

4.5.12

3490

Далас

5.5.12

3503

Далас

8.5.12

3151

Ню Орлиънс

9.4.12

3438

Ню Орлиънс

2.5.12

3471

Ню Орлиънс

4.5.12

3160

Кричим

18.4.12

3328

Кричим

26.4.12

3368

Кричим

29.4.12

3420

Кричим

1.5.12

3501

Кричим

6.5.12

Вж. също

Карта за бързи справки: VLOOKUP опреснителни

функции за търсене и препратки (справка)

Използвайте table_array аргумент във функция VLOOKUP

Разширете уменията си в Office
Преглед на обучението
Получавайте първи новите функции
Присъединете се към участниците в Office Insider

Беше ли полезна тази информация?

Благодарим ви за обратната връзка!

Благодарим ви за вашата обратна връзка. Изглежда, че ще бъде полезно да ви свържем с един от нашите агенти по поддръжката на Office.

×