Hoe kun je met VLOOKUP getallen opzoeken die als tekst zijn opgeslagen in Excel?
Bij het gebruik van VLOOKUP in Excel kan het voorkomen dat je te maken krijgt met niet-overeenkomende formaten – bijvoorbeeld wanneer een zoekwaarde is opgeslagen als tekst terwijl de kolom waarin gezocht wordt getallen bevat, of andersom. Dit kan leiden tot zoekfouten of foutmeldingen. Deze mismatch in formattering is een veelvoorkomend probleem, vooral wanneer gegevens afkomstig zijn van externe bronnen, worden geïmporteerd, of wanneer je werkt met grote en gedeelde datasets. Het oplossen van deze mismatches is essentieel om ervoor te zorgen dat VLOOKUP naar behoren werkt en je de juiste informatie kunt ophalen. Deze stap-voor-stap handleiding introduceert verschillende praktische oplossingen om deze inconsistenties in formattering aan te pakken, zodat betrouwbare en nauwkeurige zoekopdrachten gegarandeerd zijn, ongeacht hoe getallen in je werkblad zijn opgeslagen.
Dit artikel laat effectieve manieren zien om dergelijke fouten te verhelpen, inclusief formuleaanpassingen, ingebouwde Excel-tools en VBA-automatisering voor bulk- of geautomatiseerde verwerking. We bespreken ook de voordelen en overwegingen van elke methode, zodat je de meest geschikte aanpak voor jouw scenario kunt kiezen.
- VLOOKUP getallen opzoeken die als tekst zijn opgeslagen met formules
- Snel formaatconflicten oplossen met Kutools voor Excel
- VBA Macro: Standaardiseer formaten voorafgaand aan VLOOKUP
- Andere ingebouwde Excel-methoden: Gebruik 'Tekst naar kolommen' om gegevensformaten te corrigeren
VLOOKUP getallen opzoeken die als tekst zijn opgeslagen met formules
Als je zoekgegevens getallen bevatten die in één plaats als tekst zijn opgeslagen en in een andere als daadwerkelijke getallen, kan VLOOKUP falen om overeenkomsten te vinden vanwege deze formateninconsistentie. Een van de meest directe oplossingen is om Excel-formules te gebruiken die de zoekwaarde of de zoekkolom tijdens het zoeken converteren naar een consistente indeling. Deze aanpak werkt goed bij de meeste werkbladbewerkingen, is eenvoudig toe te passen en laat je originele gegevens ongewijzigd.
Als je bijvoorbeeld een zoekwaarde hebt die als tekst is opgeslagen, terwijl het bijbehorende veld in de tabel is opgemaakt als een getal, kun je de functie WAARDE gebruiken om de tekst in de VLOOKUP-formule naar een getal te converteren.
Voer de volgende formule in een lege cel in waar je het resultaat wilt weergeven:
=VLOOKUP(VALUE(G1),A2:D15,2,FALSE)Nadat je de formule hebt ingevoerd, druk op Enter om de waarde die past bij je criteria op te halen, zoals geïllustreerd in de onderstaande schermafbeelding:
Uitleg van parameters en tips:
- G1: De cel die de waarde bevat die je wilt opzoeken (kan tekst of een getal zijn).
- A2:D15: Het bereik van je datatabel dat de zoekkolom en de kolommen met de informatie die je wilt terugkrijgen bevat.
- 2: Het kolomnummer (vanaf de linkerkant van het tabelbereik) voor het resultaat dat je wilt retourneren.
Let op eventuele voorloop- of volgspaties in zoekwaarden, omdat deze ook kunnen leiden tot zoekfouten. Overweeg de functie SAMENVATEN te combineren als je gegevens mogelijk extra spaties bevatten.
Als de zoekwaarde een echt getal is (getalformaat), maar het bijbehorende veld in de tabel als tekst is opgeslagen, moet je het getal naar tekst converteren voordat je de zoekopdracht uitvoert. De functie TEKST is geschikt voor dit scenario:
=VLOOKUP(TEXT(G1,0),A2:D15,2,FALSE)Voer dit in je doelcel in, druk op Enter en het juiste resultaat zal worden weergegeven zoals hieronder getoond:
Hier zorgt de getalnotatiecode “0” binnen de TEKST-functie ervoor dat je getal wordt omgezet in een platte tekstwaarde voordat het wordt gematched.
Als je niet zeker bent over de mogelijke formaten van je zoekwaarden, of als je verwacht dat zowel tekst als getallen voorkomen in je zoekkolom, kun je beide benaderingen nesten met behulp van de ALS.FOUT-functie om alle mogelijkheden naadloos af te handelen:
=IFERROR(VLOOKUP(VALUE(G1),A2:D15,2,0),VLOOKUP(TEXT(G1,0),A2:D15,2,0))Voer deze formule in je resultaatcel in. Het zal eerst proberen de zoekopdracht uit te voeren door je waarde naar een getal te converteren; als dat mislukt (bijvoorbeeld als de waarde niet kan worden omgezet in een getal), zal het dan proberen je waarde naar tekst te converteren en opnieuw te zoeken. Dit is vooral handig in datasets met gemengde formaten of in gedeelde bestanden waar data-invoerstandaarden niet uniform zijn.
Na het invoeren van een van de bovenstaande formules, vergeet dan niet de formule naar aangrenzende cellen te kopiëren als je deze op meerdere zoekwaarden wilt toepassen – selecteer eenvoudig de cel, sleep het vulhandvat naar beneden, of gebruik Ctrl+C en Ctrl+V indien nodig. Voor grote tabellen helpen deze formules om betrouwbare matching te waarborgen zonder je originele database te wijzigen.
Deze methode biedt een flexibele en universeel toepasbare oplossing voor de meeste werkblad-gebaseerde zoekopdrachten. Echter, voor zeer grote datasets of wanneer je veel records automatisch moet verwerken, kun je overwegen om automatiseringstools zoals VBA te gebruiken voor nog grotere efficiëntie.
Snel formaatconflicten oplossen met Kutools voor Excel
Als je liever een snellere, formulevrije oplossing gebruikt, biedt Kutools voor Excel een gebruiksvriendelijk hulpmiddel genaamd Converteren tussen Tekst en Getal. Deze functie stelt je in staat om getallen die als tekst zijn opgeslagen om te zetten in echte getallen – of vice versa – met slechts een paar klikken. Het is vooral handig bij het oplossen van formaatproblemen voordat je zoekopdrachten zoals VLOOKUP of MATCH uitvoert.
Na het installeren van Kutools voor Excel, doe het volgende.
- Selecteer het bereik dat je problematische gegevens bevat (bijvoorbeeld getallen die als tekst zijn opgeslagen).
- Ga naar "Kutools" > "Tekst" > "Converteren tussen Tekst en Getal".
- In het pop-up dialoogvenster:
- Kies "Tekst naar getal" als je zoekfouten repareert die veroorzaakt worden door als tekst opgemaakte getallen. (Of selecteer "Getal naar tekst" als de zoekwaarden als tekst zijn opgeslagen.)
- Klik op "OK" om onmiddellijk het gegevensformaat te converteren.
- Kies "Tekst naar getal" als je zoekfouten repareert die veroorzaakt worden door als tekst opgemaakte getallen.
Na het converteren van tekst naar getallen zullen de geconverteerde cellen zich gedragen als echte getallen en zullen ze geen groene driehoekindicatoren meer tonen voor inconsistenties.
Deze aanpak elimineert de noodzaak van hulptabellen, formules of VBA – waardoor het ideaal is voor een snelle opruiming voordat je VLOOKUP toepast.
Kutools voor Excel - Boost Excel met meer dan 300 essentiële tools. Geniet van permanent gratis AI-functies! Nu verkrijgen
VBA Macro: Standaardiseer formaten voorafgaand aan VLOOKUP
Voor gebruikers die regelmatig met grote datasets werken, externe bestanden ontvangen of herhaalde automatisering vereisen, kan het gebruik van een eenvoudige VBA-macro programmatisch het gegevensformaat in zowel de zoekwaardekolom als de opzoekkolom standaardiseren. Op deze manier zorg je ervoor dat alle gegevens worden geconverteerd naar tekst of getal voordat je VLOOKUP uitvoert, waardoor matchingfouten door formaatverschillen worden geëlimineerd. VBA is vooral nuttig voor bulkverwerking, waardoor handmatige aanpassingen worden bespaard en gegevensconsistentie via automatisering wordt gewaarborgd.
Voordelen: Automatisering van formattering voor grote databereiken of frequente workflows; minimaliseert het risico op ontbrekende of inconsistente formattering; geschikt voor herhalende taken.
Nadelen: Niet geschikt voor gebruikers met macrobeperkingen of voor hen die niet bekend zijn met het gebruik van VBA-macro's.
Hier is hoe je een macro kunt gebruiken om celindelingen te standaardiseren:
1. Ga naar het tabblad Ontwikkelaar en klik op Visual Basic om de VBA-editor te openen. Klik in het nieuwe venster op Invoegen > Module, kopieer en plak vervolgens de volgende code in het modulegebied:
Sub StandardizeLookupFormats()
' Ask the user to select the lookup column and choose a target format
Dim rng As Range
Dim userChoice As Integer
Dim xTitleId As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.InputBox("Select the range to standardize (lookup or data column):", xTitleId, Type:=8)
If rng Is Nothing Then Exit Sub
userChoice = MsgBox("Convert selected data to Number? (Click Yes to convert to Number, No to convert to Text)", vbYesNoCancel, xTitleId)
If userChoice = vbYes Then
For Each cell In rng
If IsNumeric(cell.Value) Then
cell.Value = Val(cell.Value)
cell.NumberFormat = "General"
End If
Next
ElseIf userChoice = vbNo Then
For Each cell In rng
If Not IsEmpty(cell.Value) Then
cell.Value = CStr(cell.Value)
cell.NumberFormat = "@"
End If
Next
Else
Exit Sub
End If
End Sub2. Sluit de VBA-editor. Om de macro uit te voeren, ga terug naar Excel, druk op Alt+F8, selecteer StandardizeLookupFormats en klik op Uitvoeren.
Operatiedetails en tips:
- Deze macro zal je vragen om de kolom te selecteren (ofwel je LOOKUP-bereik of TABLE-bereik) die je wilt standaardiseren.
- Na selectie zal het je vragen of je het bereik wilt converteren naar getallen (klik Ja) of naar tekst (klik Nee). Selecteer hetzelfde formaat voor zowel je lookup- als tabelkolommen om ervoor te zorgen dat VLOOKUP betrouwbaar zal matchen.
- Nadat je deze macro hebt uitgevoerd, moet je mogelijk het werkblad opnieuw berekenen (druk op F9) of je VLOOKUP-formules opnieuw toepassen als de resultaten niet onmiddellijk verschijnen.
- Als je een foutmelding krijgt dat macro's zijn uitgeschakeld, schakel dan macro's in in je Excel-instellingen voordat je verder gaat.
Deze oplossing is ideaal voor terugkerende gegevensimporten, of wanneer je inconsistente kolommen in grote datasets opruimt voordat je VLOOKUP of andere zoekoperaties toepast.
Andere ingebouwde Excel-methoden: Gebruik 'Tekst naar kolommen' om gegevensformaten te corrigeren
Een snelle manier om getal- en tekstformaten in Excel uit te lijnen is het gebruik van de functie Tekst naar Kolommen. Dit ingebouwde hulpmiddel wordt vaak gebruikt om gegevens te splitsen, maar kan ook een formaatconversie forceren zonder formules te bewerken, wat handig is wanneer je een eenmalige reparatie wilt of wanneer je te maken hebt met eenvoudige lijsten.
Voordelen: Heel eenvoudig, geen formules of code nodig, behoudt de originele gegevensstructuur; Nadelen: Geschikt voor eenmalige correcties, wordt niet automatisch bijgewerkt als gegevens veranderen.
Om deze methode te gebruiken om getallen die als tekst zijn opgeslagen (of vice versa) in een kolom te converteren:
- Selecteer de kolom met het vermoedelijke inconsistente formaat (bijvoorbeeld je zoekkolom of de kolom waarnaar VLOOKUP verwijst).
- Op het tabblad Gegevens, klik op Tekst naar Kolommen.
- In de wizard, kies Gescheiden, en klik vervolgens op Volgende.
- Schakel alle scheidingstekencheckboxen uit (aangezien je de gegevens niet splitst); klik op Volgende.
- In Kolomgegevensformaat, selecteer Algemeen (om Excel te dwingen getallen als getallen te herkennen) of selecteer Tekst (om getallen naar tekst te converteren).
- Klik op Voltooien om het proces te voltooien.
Zodra dit is gedaan, zal je gegevens hun formaat geforceerd hebben uitgelijnd naar getallen of tekst, waardoor VLOOKUP-mismatches worden opgelost. Controleer altijd een paar cellen om te bevestigen dat de conversie zoals verwacht heeft gewerkt. Indien nodig herhaal het proces voor zowel je zoekkolom als je zoekwaarden om consistentie te maximaliseren.
Praktische tips: “Tekst naar Kolommen” wijzigt gegevens direct, dus het kan celinhoud overschrijven als je bestaande gegevens direct rechts hebt staan. Overweeg je kolom eerst naar een leeg gebied te kopiëren als je niet zeker bent, en sla altijd een back-up van je bestand op voordat je bulkgegevenshulpmiddelen gebruikt.
Beste productiviteitstools voor Office
Verbeter je Excel-vaardigheden met Kutools voor Excel en ervaar ongeëvenaarde efficiëntie. Kutools voor Excel biedt meer dan300 geavanceerde functies om je productiviteit te verhogen en tijd te besparen. Klik hier om de functie te kiezen die je het meest nodig hebt...
Office Tab brengt een tabbladinterface naar Office en maakt je werk veel eenvoudiger
- Activeer tabbladbewerking en -lezen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
- Verhoog je productiviteit met50% en bespaar dagelijks honderden muisklikken!
Alle Kutools-invoegtoepassingen. Eén installatieprogramma
Kutools for Office-suite bundelt invoegtoepassingen voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro, ideaal voor teams die werken met Office-toepassingen.
- Alles-in-één suite — invoegtoepassingen voor Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Eén installatieprogramma, één licentie — in enkele minuten geïnstalleerd (MSI-ready)
- Werkt beter samen — gestroomlijnde productiviteit over meerdere Office-toepassingen
- 30 dagen volledige proef — geen registratie, geen creditcard nodig
- Beste prijs — bespaar ten opzichte van losse aanschaf van invoegtoepassingen