Niille teistä, jotka tuntevat Excelin^(Excel) hyvin , olet todennäköisesti hyvin perehtynyt VLOOKUP - funktioon. VHAKU - funktiota^(VLOOKUP) käytetään arvon etsimiseen eri solusta saman rivin vastaavan tekstin perusteella.

Jos olet vielä uusi VLOOKUP - toiminnossa, voit katsoa edellisen viestini VLOOKUPin käyttämisestä Excelissä^{(how to use VLOOKUP in Excel)} .

Niin tehokas kuin se on, VLOOKUPilla^(VLOOKUP) on rajoitus siihen, kuinka täsmäytysviitetaulukko on rakennettava, jotta kaava toimisi.

Tämä artikkeli näyttää rajoituksen, jossa VLOOKUPia^(VLOOKUP) ei voida käyttää, ja esittelee Excelin^(Excel) toisen toiminnon nimeltä INDEX-MATCH , joka voi ratkaista ongelman.

INDEX MATCH Excel Esimerkki

Käytämme seuraavaa esimerkki Excel-laskentataulukkoa , meillä on luettelo auton omistajista ja auton nimistä. Tässä esimerkissä yritämme napata  auton ^{(Car Model)}tunnuksen^{(Car ID)} , joka perustuu useiden omistajien alla lueteltuun automalliin, kuten alla on esitetty:

Erillisellä CarType- nimisellä arkilla meillä on yksinkertainen autotietokanta, jossa on  tunnus^(ID) , auton malli^{(Car Model)} ja väri^(Color) .

Tällä taulukon asetuksella  VLOOKUP - toiminto voi toimia vain, jos tiedot, jotka haluamme hakea, sijaitsevat sarakkeessa sen oikealla puolella, mitä yritämme täsmäyttää ( Automalli^{(Car Model )} - kenttä).

Toisin sanoen tällä taulukkorakenteella, koska yritämme yhdistää sitä automallin perusteella , ainoa tieto, jonka voimme saada, on väri^{(Color )} (ei tunnus^(ID) , koska ID -  sarake sijaitsee Automalli-sarakkeen vasemmalla  puolella ^{(Car Model)}.^{(Car Model )} )

Tämä johtuu siitä, että VLOOKUP -sovelluksella hakuarvon^(VLOOKUP) on oltava ensimmäisessä sarakkeessa ja hakusarakkeiden on oltava oikealla. Mikään näistä ehdoista ei täyty esimerkissämme.

Hyvä uutinen on, että INDEX-MATCH  voi auttaa meitä saavuttamaan tämän. Käytännössä tämä on itse asiassa kahden Excel- funktion yhdistäminen, jotka voivat toimia erikseen: INDEX- funktio ja MATCH- funktio.

Tässä artikkelissa puhumme kuitenkin vain näiden kahden yhdistelmästä, jonka tarkoituksena on toistaa VLOOKUP :n toiminto .

Kaava voi aluksi tuntua hieman pitkältä ja pelottavalta. Kuitenkin, kun olet käyttänyt sitä useita kertoja, opit syntaksin ulkoa.

Tämä on koko kaava esimerkissämme:

=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))

Tässä on kunkin osion erittely

=INDEX( – “=” osoittaa kaavan alkua solussa ja INDEX on käyttämämme ^(INDEX)Excel - funktion ensimmäinen osa .

CarType!$A$2:$A$5CarType -arkin sarakkeet,  joissa noudettavat tiedot ovat. Tässä esimerkissä  kunkin automallin ^{(Car Model.)}tunnus .^(ID)

MATCH( – käyttämämme ^{(MATCH( )}Excel - funktion toinen osa .

B4 – Solu, joka sisältää käyttämämme hakutekstin ( Automalli^{(Car Model)} ) .

CarType!$B$2:$B$5CarType - arkin sarakkeet,   joissa on tiedot, joita käytämme vastaamaan hakutekstiä.

0)) – Osoittaa, että hakutekstin on vastattava täsmälleen vastaavassa sarakkeessa olevaa tekstiä (esim CarType!$B$2:$B$5 ). Jos tarkkaa vastaavuutta ei löydy, kaava palauttaa #N/A .

Huomaa: muista kaksoissulkeet tämän funktion lopussa "))" ja pilkut argumenttien välillä.^{(Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.)}

Henkilökohtaisesti olen siirtynyt pois VLOOKUPista^(VLOOKUP) ja käytän nyt INDEX-MATCHia, koska se pystyy tekemään enemmän kuin VLOOKUP .

INDEX - MATCH- toiminnoilla on myös muita etuja ^{(INDEX-MATCH)}VLOOKUPiin^(VLOOKUP) verrattuna :

1. Nopeammat laskelmat^{(Faster Calculations)}

Kun käsittelemme suuria tietojoukkoja, joissa itse laskenta voi kestää kauan monien VLOOKUP - funktioiden vuoksi, huomaat, että kun korvaat kaikki nämä kaavat INDEX-MATCH -komennolla^{(INDEX-MATCH)} , kokonaislaskenta lasketaan nopeammin.

1. Suhteellisia sarakkeita ei tarvitse laskea^{(No Need to Count Relative Columns)}

Jos viitetaulukossamme on avainteksti, jota haluamme etsiä sarakkeesta C ja tiedot, jotka meidän on saatava, ovat sarakkeessa AQ , meidän on tiedettävä/laskettava, kuinka monta saraketta on sarakkeen C ja sarakkeen AQ välillä käytettäessä VLOOKUP-ohjelmaa^(VLOOKUP) . .

INDEX-MATCH- funktioiden avulla  voimme valita suoraan indeksisarakkeen (eli sarakkeen AQ), josta meidän tulee saada tiedot, ja valita täsmäytettävä sarake (eli sarake C).

1. Se näyttää monimutkaisemmalta^{(It Looks More Complicated)}

VLOOKUP on nykyään melko yleinen, mutta monet eivät tiedä INDEX-MATCH-toimintojen käyttämisestä yhdessä.

INDEX-MATCH- funktion pidempi merkkijono auttaa saamaan sinut näyttämään asiantuntijalta monimutkaisten ja edistyneiden Excel - toimintojen käsittelyssä. Nauttia!

When to Use Index-Match Instead of VLOOKUP in Excel

For those оf you who are well-vеrsed in Excel, you are most likelу very familiar with the VLOOKUP function. The VLOOKUP function is used to find a value in a different cell based on some matching text within the same row.

If you are still new to VLOOKUP function, you can check out my previous post on how to use VLOOKUP in Excel.

As powerful as it is, VLOOKUP has a limitation on how the matching reference table needs to be structured in order for the formula to work.

This article will show you the limitation where VLOOKUP cannot be used and introduce another function in Excel called INDEX-MATCH that can solve the issue.

INDEX MATCH Excel Example

Using the following example Excel spreadsheet, we have a list of car owners name and the car name. In this example, we will be trying to grab the Car ID based on the Car Model listed under multiple owners as shown below:

On a separate sheet called CarType, we have a simple car database with the ID, Car Model and Color.

With this table setup, the VLOOKUP function can only work if the data that we want to retrieve is located on the column to the right of what we are trying to match (Car Model field).

In other words, with this table structure, since we are trying to match it based on the Car Model, the only information that we can get is Color (Not ID as the ID column is located to the left of the Car Model column.)

This is because with VLOOKUP, the lookup value must appear in the first column and the lookup columns have to be to the right. None of those conditions are met in our example.

The good news is, INDEX-MATCH will be able to help us in achieving this. In practice, this is actually combining two Excel functions that can work individually: INDEX function and MATCH function.

However, for the purpose of this article, we will only talk about the combination of the two with the aim of replicating the function of VLOOKUP.

The formula can seem to be a little bit long and intimidating at first. However, once you have used it several times, you will learn the syntax by heart.

This is the full formula in our example:

=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))

Here is the breakdown for each section

=INDEX( – The “=” indicates the beginning of formula in the cell and INDEX is the first part of the Excel function that we are using.

CarType!$A$2:$A$5 – the columns on sheet CarType where the data we would like to retrieve is contained. In this example, the ID of each Car Model.

MATCH( – The second part of the Excel function that we are using.

B4 – The cell that contain search text that we are using (Car Model).

CarType!$B$2:$B$5 – The columns on sheet CarType  with the data which we will use to match against the search text.

0)) – To indicate that the search text has to exactly match with the text in the matching column (i.e. CarType!$B$2:$B$5). If the exact match is not found, the formula returns #N/A.

Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.

Personally I have moved away from VLOOKUP and now use INDEX-MATCH as it is capable of doing more than VLOOKUP.

The INDEX-MATCH functions also have other benefits as compared to VLOOKUP:

1. Faster Calculations

When we are working with large datasets where the calculation itself can take a long time due to many VLOOKUP functions, you will find that once you replace all of those formulas with INDEX-MATCH, the overall calculation will be compute faster.

1. No Need to Count Relative Columns

If our reference table is having the key text that we want to search in column C and the data that we need to get is in column AQ, we will need to know/count how many columns are between column C and column AQ when using VLOOKUP.

With the INDEX-MATCH functions, we can directly select the index column (i.e column AQ) where we need to get the data and select the column to be matched (i.e. column C).

1. It Looks More Complicated

VLOOKUP is quite common nowadays, but not many know about using the INDEX-MATCH functions together.

The longer string in INDEX-MATCH function help to make you look like an expert in handling complex and advanced Excel functions. Enjoy!

Related posts

• Kuinka korjata #N/A-virheet Excel-kaavoissa, kuten VLOOKUP

• Kuinka poistaa tyhjiä rivejä Excelissä

• Excelin Speak Cells -ominaisuuden käyttäminen

• Kuinka lisätä Excel-laskentataulukko Word-dokumenttiin

• Excelin mitä jos -analyysin käyttäminen

• Kuinka laskea Z-pisteet Excelissä

• Solujen, sarakkeiden ja rivien yhdistäminen Excelissä

• Excelin mitä jos -analyysitavoitteenhakutyökalun käyttäminen

• Kirjoita numerot nopeasti Wordissa ja Excelissä

• Kuinka kirjoittaa IF-kaava/-lauseke Excelissä

• Kuinka siirtää sarakkeita Excelissä

• If- ja sisäkkäisten If-lauseiden käyttäminen Excelissä

• 5 tapaa muuntaa tekstiä numeroiksi Excelissä

• Kuinka lisätä CSV tai TSV Excel-laskentataulukkoon

• 10 Excel-vinkkiä ja temppuja vuodelle 2019

• Erot Microsoft Excel Onlinen ja Excel For Desktopin välillä

• Kuinka lajitella päivämäärän mukaan Excelissä

• 4 tapaa käyttää valintamerkkiä Excelissä

• Mikä on VBA-taulukko Excelissä ja kuinka ohjelmoida yksi

• Kuinka yhdistää tietoja useisiin Excel-tiedostoihin