Vraag over Excel 2003 verticaal zoeken

[DJC001]

Beste forumleden,

Ik heb een vraag. Ik werk met Excel 2003. In de bijlage een bestand waar ik een vraag over heb.

TAB1
kolom A staan gegevens die uit een ander Excel bestand komen. Deze gegevens kunnen bestaan uit getallen, letters of een combinatie er van. De hoeveelheid tekens in de cel kan ook variëren zoals in het bestand te zien is.
Kolom B dit zijn bedragen

TAB2
Kolom A Hier staan opnieuw enkele gegevens die uit een programma komen.
Kolom C Hier gaat het mis. Met de functie verticaal zoeken moeten hier de bedragen uit TAB1 komen te staan. Zoals jullie kunnen zien bevatten sommige cellen een fout. Het gaat om de cellen die starten met een cijfer. De formule werkt namelijk hier niet mee. Converteer ik de cel naar een getal dan werkt de formule wel. Maar aangezien het 1000en rijen zijn en gegevens in deze rij totaal van elkaar verschillen is het onmogelijk om alles te gaan converteren. Het probleem met converteren is ook dat als een cel start met een 0 de 0 wordt verwijderd uit de cel. Dat moet niet.Bekijk bijlage Map1.xls

Hoe krijg ik dit werkbaar? Let op ik werk dus met Excel 2003.

 

[AlexCEL]

Probeer deze eens:

C1: =ALS.FOUT(AFRONDEN(INDEX(TAB1!$B:$B;KLEINSTE(ALS(ISGETAL(VIND.SPEC(A1;TAB1!$A$1:$A$60000;1));RIJ($A$1:$A$60000);10^99);1))/B1;2);"")

Ik kan zelf niet testen of deze in XL2003 ook werkt helaas.

Let op: het is een matrixfunctie, d.w.z. afsluiten met Control+Shift+Enter

 

[lam201]

Of met deze functie.
Deze zet de getallen om naar tekst, om een match te kunnen maken op TAB1

=ROUND(VLOOKUP([B]TEXT([/B]A2[B];"0")[/B];TAB1!$A$1:$B$60000;2;0)/B2;2)

of in NL: vervang de verwijzing naar A2 door TEKST(A2;"0")

Lambert

 

[AlexCEL]

Veel simpeler Lambert... dus beter. Dacht te moeilijk.

NL: =AFRONDEN(VERT.ZOEKEN(TEKST(A1;"0");TAB1!$A$1:$B$60000;2;0)/B1;2)

Misschien nog iets toevoegen wat te doen als de waarde niet gevonden wordt.

 

[E v R]

Om helemaal zeker te zijn; ook de tabel alles als tekstwaarde zetten:
Wel afsluiten met CSE

=ROUND(VLOOKUP(A1&"";TAB1!A$1:B$60000&"";2;0)/B1;2)

 

[DJC001]

Bedankt voor jullie reacties. De formule van Lambert lijkt me inderdaad het beste!

Eric jouw formule werkt niet in Excel 2003 (???). Maar wat bedoel je precies met om helemaal zeker te zijn?

Nogmaals bedankt!

 

[E v R]

Je moet die formule wel afsluiten met CTRL+Shift+Enter
Om helemaal zeker te zijn:

Nu maak je van de zoekwaarde in Tab2 een tekstwaarde, dan ga je er dus vanuit dat in jouw zoekbereik dus ook alle zoekwaarden tekstwaarden zijn, als dit dus niet het geval is (en dat kan al snel gebeuren door in een cel te klikken) maakt mijn suggestie ook van alle waarden in het zoekbereik tekstwaarden.

 

[DJC001]

Ok super bedankt! Nu werkt de formule wel. Misschien een overbodige vraag maar kan ik de verticaal zoeken formule ook in volledige kolommen toepassen?

Dus kan deze formule:

=VERT.ZOEKEN(A1;Bron!$A$1:$D$50000;4;0)

ook zo gebruikt worden:

=VERT.ZOEKEN(A1;Bron!A:D;4;0)

 

[DJC001]

Nog een vraag... Ik probeer het te begrijpen maar het lukt nog niet helemaal

Eric, jij schrijft: nu maak je van de zoekwaarde in Tab2 een tekstwaarde. Wordt dan TAB1 Kolom A omgezet naar text of TAB2 kolom A? Of beide?

Want als ik in een cel op de rechtermuisknop klik in TAB1 en TAB2 op kolom A zie ik dat alle cellen als celeigenschap Getal hebben. Ook de codes die alleen letters hebben én ook de cellen waar een groene foutmelding bij staat "Getal opgeslagen als text" hebben als celeigenschap getal. Hoe kan nou iets dat opgeslagen is als text toch eigenschap getal hebben?

 

[E v R]

Ok super bedankt! Nu werkt de formule wel. Misschien een overbodige vraag maar kan ik de verticaal zoeken formule ook in volledige kolommen toepassen?

Dus kan deze formule:

=VERT.ZOEKEN(A1;Bron!$A$1:$D$50000;4;0)

ook zo gebruikt worden:

=VERT.ZOEKEN(A1;Bron!A:D;4;0)

Gebruik geen hele kolommen, maar kijk eens naar een tabel.

 

[E v R]

Eric, jij schrijft: nu maak je van de zoekwaarde in Tab2 een tekstwaarde. Wordt dan TAB1 Kolom A omgezet naar text of TAB2 kolom A? Of beide?

In de formule uit post #3 wordt enkel van de zoekwaarde (cel A1 uit TAB2) in de formule een tekstwaarde gemaakt

In de formule uit post #5 wordt en van de zoekwaarde (cel A1 uit TAB2) in de formule een tekstwaarde gemaakt alsmede alle cellen uit kolom A uit Tab1

Want als ik in een cel op de rechtermuisknop klik in TAB1 en TAB2 op kolom A zie ik dat alle cellen als celeigenschap Getal hebben. Ook de codes die alleen letters hebben én ook de cellen waar een groene foutmelding bij staat "Getal opgeslagen als text" hebben als celeigenschap getal. Hoe kan nou iets dat opgeslagen is als text toch eigenschap getal hebben?

Ja dat klopt, maar klik nu eens in cel A1 op TAB1 en druk op enter, nu is dit dus een getalwaarde geworden, vandaar mijn opmerking "om helemaal zeker te zijn"

 

[DJC001]

Dag Eric,

Het is me nu duidelijk! Bedankt voor je heldere uitleg.

Nog 1 vraag over het zoeken in hele kolommen. Ik vraag dit namelijk omdat ik dit nooit op Internet tegen kom. In voorbeelden wordt altijd een bereik gebruikt. Maar als ik in Excel een formule wil maken lijkt het wel te werken. Ik heb enkele documenten waarbij ik het wel toegepast heb maar dit is een belangrijk document waarbij ik zeker moet weten dat het goed werkt.

Ik wil het zoeken in hele kolommen gebruiken omdat ik een standaard Excel document voor maandelijks terugkerende taken waarbij het bereik maandelijks anders is. Het lijkt mij dan makkelijk om de formule in hele kolommen te gebruiken omdat ik dan niet de formule telkens hoef aan te passen.

Kun je aangeven waarom je zegt geen hele kolommen? Wordt dit Microsoft afgeraden?

 

[E v R]

Je werkt nu met XL2003, die heeft 65K rijen, latere versies hebben 16 keer zoveel rijen, en dat gaat formules vertragen
Gewoon goed gebruik, het werkt wel maar
bij een tabel heb je altijd het juiste aantal rijen.