Изтеглете примерната работна книга
Този урок ще ви научи как да извличате данни от множество колони, като използвате функциите MATCH и VLOOKUP в Excel и Google Sheets.
Защо трябва да комбинирате VLOOKUP и MATCH?
Традиционно, когато използвате функцията VLOOKUP, въвеждате a индекс номер на колона за да определите от коя колона да извличате данни.
Това създава два проблема:
- Ако искате да изтеглите стойности от няколко колони, трябва ръчно да въведете индекс номер на колона за всяка колона
- Ако вмъквате или премахвате колони, вашият индекс номер на колона вече няма да е валидна.
За да направите вашата VLOOKUP функция динамична, можете да намерите индекс номер на колона с функцията MATCH.
| 1 | = VLOOKUP (G3, B3: E5, MATCH (H2, B2: E2,0), FALSE) |
Нека да видим как работи тази формула.
MATCH функция
Функцията MATCH ще върне индекс номер на колона от желаната от вас заглавка на колона.
В примера по -долу индексният номер на колоната за „Възраст“ се изчислява от функцията MATCH:
| 1 | = MATCH ("Възраст", B2: E2,0) |
„Възраст“ е заглавката на втората колона, така че 2 се връща.
Забележка: Последният аргумент на функцията MATCH трябва да бъде зададен на 0, за да се извърши точно съвпадение.
Функция VLOOKUP
Сега можете просто да включите резултата от функцията MATCH във вашата функция VLOOKUP:
| 1 | = VLOOKUP (G3, B3: E5, H3, FALSE) |
Замяната на аргумента за индекс на колоната с функцията MATCH ни дава оригиналната формула:
| 1 | = VLOOKUP (G3, B3: E5, MATCH (H2, B2: E2,0), FALSE) |
Вмъкване и изтриване на колони
Сега, когато вмъкнете или изтриете колони в диапазона от данни, резултатът от формулата ви няма да се промени.
В горния пример добавихме Учител колона към диапазона, но все пак искате студентската Възраст. Резултатът от функцията MATCH идентифицира, че „Възраст“ вече е 3 -тият елемент в диапазона на заглавките, а функцията VLOOKUP използва 3 като индекс на колоната.
Заключване на препратки към клетки
За да направим нашите формули по -лесни за четене, показахме формулите без заключени клетъчни препратки:
| 1 | = VLOOKUP (G3, B3: E5, MATCH (H2, B2: E2,0), FALSE) |
Но тези формули няма да работят правилно, когато копирате и поставите другаде във вашия файл. Вместо това трябва да използвате заключени клетъчни препратки по следния начин:
| 1 | = VLOOKUP ($ G3, $ B $ 3: $ E $ 5, MATCH (H $ 2, $ B $ 2: $ E $ 2,0), FALSE) |
Прочетете нашата статия за Заключване на препратки към клетки, за да научите повече.
VLOOKUP & MATCH Комбинирани в Google Sheets
Тези формули работят абсолютно същите в Google Sheets, както и в Excel.
