Kirjainkoolla erottava VLOOKUP - Excel ja Google Sheets

Lataa esimerkkityökirja

Lataa esimerkkityökirja

Kirjainkoolla merkitsevä VLOOKUP - Excel

Tämä opetusohjelma osoittaa, miten kirjainkoolla erottava VLOOKUP suoritetaan Excelissä kahdella eri menetelmällä.

Menetelmä 1 - kirjainkoolla merkitsevä VLOOKUP, jossa on avustava sarake

= VLOOKUP (MAX (EXACT (E2, $ B $ 2: $ B $ 7)*(ROW ($ B $ 2: $ B $ 7))), $ C $ 2: $ D $ 7,2,0)

VLOOKUP -toiminto

VLOOKUP -toimintoa käytetään etsimään likimääräinen tai tarkka vastaavuus alueen vasemmanpuoleisen sarakkeen arvolle ja palautetaan vastaava arvo toisesta sarakkeesta. Oletuksena VLOOKUP toimii vain sellaisilla arvoilla, jotka eivät erota kirjaimista, kuten:

Kirjainkoolla herkkä VLOOKUP

Yhdistämällä VLOOKUP, EXACT, MAX ja ROW voimme luoda kirjainkokoherkän VLOOKUP-kaavan, joka palauttaa vastaavan arvon kirjainkoolla merkitsevälle VLOOKUP: lle. Käydään esimerkki läpi.

Meillä on luettelo tuotteista ja niitä vastaavista hinnoista (huomaa, että nimikkeen isot ja pienet kirjaimet erottavat toisistaan):

Olettaen, että meitä pyydetään saamaan tuotteen hinta käyttämällä sen tuotetunnusta seuraavasti:

Tämän saavuttamiseksi meidän on ensin luotava auttajasarake ROW:

= ROW () napsauta ja vedä (tai kaksoisnapsauta) täyttääksesi kaikki alueen rivit

Yhdistä seuraavaksi VLOOKUP, MAX, EXACT ja ROW seuraavanlaiseen kaavaan:

= VLOOKUP (MAX (EXACT (,)*(ROW ())), ,, 0)

= VLOOKUP (MAX (EXACT (E2, $ B $ 2: $ B $ 7)*(ROW ($ B $ 2: $ B $ 7))), $ C $ 2: $ D $ 7,2,0)

Miten kaava toimii?

1. EXACT -funktio tarkistaa kohteen E2 (hakuarvo) tuotetunnuksen B2: B7 (hakualue) -arvojen kanssa ja palauttaa taulukon TOSI, jos taulukossa on {FLASE, FLASE, FLASE, FLASE, FLASE, TRUE}.
2. Tämä taulukko kerrotaan sitten ROW -taulukolla {2, 3, 4, 5, 6, 7} (huomaa, että tämä vastaa auttajasaraketta).
3. MAX -funktio palauttaa tuloksena olevan taulukon maksimiarvon {0,0,0,0,0,7}, joka on esimerkissämme 7.
4. Sitten käytämme tulosta hakuarvona VLOOKUP -ohjelmassa ja valitsemme hakualueeksi auttajasarakkeen. Esimerkissämme kaava palauttaa vastaavan arvon 16,00 dollaria.

Menetelmä 2 - kirjainkoolla merkitsevä VLOOKUP, jossa on "virtuaalinen" auttajasarake

= VLOOKUP (MAX (EXACT (D2, $ B $ 2: $ B $ 7)*(ROW ($ B $ 2: $ B $ 7))), CHOOSE ({1,2}, ROW ($ B $ 2: $ B $ 7)) , $ C $ 2: $ C $ 7), 2,0)

Tämä menetelmä käyttää samaa logiikkaa kuin ensimmäinen menetelmä, mutta eliminoi tarpeen avustajasarakkeen luomiseen ja luo sen sijaan VALINTA- ja RIVI -muodon "virtuaalisen" auttajasarakkeen luomiseksi seuraavasti:

= VLOOKUP (MAX (EXACT (,)*(ROW ())), CHOOSE ({1,2}, ROW (),),, 0)

= VLOOKUP (MAX (EXACT (D2, $ B $ 2: $ B $ 7)*(ROW ($ B $ 2: $ B $ 7))), CHOOSE ({1,2}, ROW ($ B $ 2: $ B $ 7)) , $ C $ 2: $ C $ 7), 2,0)

Miten kaava toimii?

1. Kaavan ensimmäinen osa toimii samalla tavalla kuin ensimmäinen menetelmä.
2. Yhdistämällä CHOOSE ja ROW palauttaa taulukon, jossa on kaksi saraketta, yksi rivinumeroa ja toinen hintaa varten. Matriisi on erotettu puolipisteellä seuraavan rivin ja pilkun kanssa seuraavalla sarakkeella: {2,45; 3,83; 4,23; 5,74; 6,4; 7,16}.
3. Voimme sitten käyttää VLOOKUPin kaavan ensimmäisen osan tulosta löytääksesi vastaavan arvon CHOOSE- ja ROW -matriisistamme.

Kirjainkoolla erottava VLOOKUP Google Sheetsissä

Kaikki yllä olevat esimerkit toimivat täsmälleen samalla tavalla Google Sheetsissä kuin Excelissä.