Edistyneet XLOOKUP-sovellukset

Likimääräisen vastaavuuden käyttäminen XLOOKUP-toiminnolla

Tarkka vastaavuus toimii, kun tunnisteiden on vastattava täydellisesti. Joissakin liiketoimintatilanteissa tarvitaan kuitenkin arvojen vastaavuutta tietyissä rajoissa.

Esimerkki: provisioprosentit myyntirajojen perusteella.

Sales Table

Jos myyjällä on 12 000 myyntiä, provisioprosentin tulee olla 8 %. Tämä edellyttää likimääräistä vastaavuutta.

Syntaksi Match Mode -asetuksella

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], match_mode)

match_mode-vaihtoehdot:

• 0: täsmällinen osuma;
• -1: täsmällinen osuma tai seuraava pienempi arvo;
• 1: täsmällinen osuma tai seuraava suurempi arvo;
• 2: jokerimerkkihaku.

Aluepohjaisissa hauissa käytä arvoa -1. Tämä tarkoittaa, että jos täsmällistä osumaa ei löydy, palautetaan seuraava pienempi arvo.

Likimääräisen vastaavuuden käyttäminen

Oletetaan, että käytössä on arvosanataulukko. Column A sisältää vaaditun vähimmäispistemäärän. Column B sisältää vastaavan kirjainarvosanan. Opiskelijan pistemäärä syötetään soluun D2. Tavoitteena on palauttaa oikea kirjainarvosana pistemäärän perusteella. Vähimmäispistemäärä-sarakkeen tulee olla nousevassa järjestyksessä.

=XLOOKUP(D2; A3:A8; B3:B8; "Not Found"; -1)

• D2: sisältää opiskelijan pistemäärän;
• A3:A8: sisältää vähimmäispisterajat;
• B3:B8: sisältää kirjainarvosanat;
• "Not Found": määrittää, mitä näytetään, jos vastaavuutta ei löydy;
• -1: ohjeistaa Exceliä palauttamaan lähimmän pienemmän arvon, jos täsmällistä vastaavuutta ei löydy.

Usean sarakkeen palauttaminen

Oletetaan, että käytössä on työntekijätaulukko: Employee ID | Salary | Bonus. Tavoitteena on palauttaa sekä Salary että Bonus yhdellä haulla.

=XLOOKUP(A3;E3:E7;F3:G7)

• A3: sisältää Employee ID-tunnuksen;
• E3:E7: sisältää Employee ID-tunnukset hakutaulukossa;
• F3:G7: sisältää kaksi saraketta, Salary ja Bonus.

Haku alhaalta ylöspäin

Oletetaan, että työntekijätaulukossa on useita samoja Employee ID -tunnuksia ajan mittaan. Uusin tietue lisätään aina taulukon loppuun. Oletuksena XLOOKUP hakee ylhäältä alas. Palauttaaksesi uusimman tietueen, hae alhaalta ylöspäin.

=XLOOKUP(A2; E3:E7; F3:F7; "Not Found"; 0; -1)

• A2: haettava Employee ID;
• E3:E7: hakusarake;
• F3:F7: palautussarake;
• 0: pakottaa tarkan osuman;
• -1: hakee alhaalta ylöspäin.

Tehtävä

Tilanne

Työkirjassa on kolme erillistä taulukkoa.

Tavoitteena on käyttää kehittynyttä XLOOKUP-funktiota saadaksesi tarkat tulokset.