Excel (vertical lookup?)

[KaiseRRuby]

Loop tegen een probleem aan.

Heb een excel lijst met 34.000 regels. Die regels zijn gesorteerd op een EAN. Dat is kolom 1. Van die 34.000 wil ik specifiek de gegevens hebben van ongeveer 800 regels.

Ik heb nu twee tabbladen. De eerste genaamd 'alles', de tweede genaamd 'specifiek'. In het tabblad specifiek staan de 800 EAN's waarvan ik de gegevens wil hebben.

Nu wil ik graag dat Excel automatisch de gegevens van die 800 uit die 34000 (dus in tabblad 'alles') zoekt en die in kolommen erachter zet. In de het tabblad 'alles' staat het ook gesorteerd in kolommen.

Ik heb zitten Googlen en daar kom ik op de optie vertical lookup maar dat is absoluut geen succes, krijg dat niet werkend.

Iemand tips om dit voor elkaar te krijgen?

[TheRookie]

Hoe ver ben je gekomen met de vlookup functie en waar loop je op vast ?
Op basis van je beschrijving lijkt het in elk geval dat je met VLOOKUP gaat kunnen wat je wil

[StecaGrid]

Ik zou het zelf doen met verticaal zoeken (ean1;bereik;kolomgetal)
Evt kan je het kolomgetal ook boven iedere kolom zetten als het om veel kolommen gaat. Hou er rekening mee dat de eans in je bereik wel alfabetisch gesorteerd zijn.

[thefal]

Ik heb even snel een sample Excelletje gemaakt. In kolom A heb ik EAN nummers, in kolom B voedselnamen, in kolom C de prijs.

In het 2e tabblad heb ik enkele EAN nummers overgenomen in kolom A. Vervolgens heb ik in kolom B de volgende formule geplakt (helaas Nederlands):
=VERT.ZOEKEN(A1;alles!$A$2:$B$9;2)

Daarbij is:
VERT.ZOEKEN de functie (VLOOKUP in het engels)
A1: verwijzijng naar de kolom met het EAN nummer waar we naar zoeken
alles!$ verwijzing naar het eerste tabblad
$A$2:$B$9: De originele tabel. Dmv de dollartekens zorg je dat het bereik hetzelfde blijft als je de formule in een andere cel plakt
2: We willen de 2e kolom hebben (de namen)

Vervolgens heb ik in C1 de formule
=VERT.ZOEKEN(A1;alles!$A$2:$C$9;3)

Precies hetzelfde als bovenstaande, maar nu met een 3 omdat ik de 3e kolom wil hebben.

In het Engels worden dit volgens mij de formules:
=VLOOKUP(A1,alles!$A$2:$B$9,2)
=VLOOKUP(A1,alles!$A$2:$B$9,3)

[ Voor 8% gewijzigd door thefal op 09-07-2015 15:33 ]

[KaiseRRuby]

Bedankt allen!

Het lukt me nog steeds niet.

Ik wil het volgende, specifieker uitgelegd. Kolom B t/m G overhalen uit het tabblad 'alles' naar 'specifiek'. Dat kan denk ik niet direct, dus ik moet iedere kolom apart doen denk ik.

Kortom: dit is mijn formule =VERT.ZOEKEN(A2;alles!A:A;alles!C:C;onwaar)

Ik verwacht dit:

Hij zoekt de (in A2 genoemde) EAN code op. Hij kopieert de waarde die bij C gegeven wordt en plakt deze waarde in B2. Ik herhaal deze formule als volgt tot aan kolom G. Dus.

Hij zoekt de (in A2 genoemde) EAN code op. Hij kopieert de waarde die bij D gegeven wordt en plakt deze waarde in C2.

Hij zoekt de (in A2 genoemde) EAN code op. Hij kopieert de waarde die bij E gegeven wordt en plakt deze waarde in D2.

Etc..

[Angeloonie]

Je hebt duidelijk de hulpfunctie nog niet gebruikt..

A2 is goed (als dit de cel met het EAN is die je wilt opzoeken in alles!)
alles!A:A is fout, dit moet de volledige range zijn waaruit je gegevens wilt zoeken EN ophalen. Dus alles!A:G
alles!C:C is fout, dit moet een GETAL zijn, bijv. 3 voor kolom C als je bereik A:G is, 4 voor kolom D etc.

Dus:
=VERT.ZOEKEN(A2;alles!A:G;3;onwaar) voor waarde uit kolom C
=VERT.ZOEKEN(A2;alles!A:G;4;onwaar) voor waarde uit kolom D
=VERT.ZOEKEN(A2;alles!A:G;5;onwaar) voor waarde uit kolom E

[ Voor 32% gewijzigd door Angeloonie op 10-07-2015 16:46 ]

[StecaGrid]

Je alles! C:C moet het getal 3 dijn

[Sparhawk]

KaiseRRuby schreef op vrijdag 10 juli 2015 @ 16:32:
Bedankt allen!

Het lukt me nog steeds niet.

Wat lukt niet? krijg je 1 lookup wel over (dus de prijs van de komkommer?)

Ik wil het volgende, specifieker uitgelegd. Kolom B t/m G overhalen uit het tabblad 'alles' naar 'specifiek'. Dat kan denk ik niet direct, dus ik moet iedere kolom apart doen denk ik.

Je moet inderdaad iedere kolom apart doen, dus 6 aparte formules aanmaken

Kortom: dit is mijn formule =VERT.ZOEKEN(A2;alles!A:A;alles!C:C;onwaar)

Volgens mij maak je nog een fout in je formule. Ik gebruik de engelse excel, maar kun je eens met behulp van het formule icoon de vlookup maken?

De functie Vlookup heeft 4 input parameters.

Ik verwacht dit:

Hij zoekt de (in A2 genoemde) EAN code op. Hij kopieert de waarde die bij C gegeven wordt en plakt deze waarde in B2. Ik herhaal deze formule als volgt tot aan kolom G. Dus.

Hij zoekt de (in A2 genoemde) EAN code op. Hij kopieert de waarde die bij D gegeven wordt en plakt deze waarde in C2.

Hij zoekt de (in A2 genoemde) EAN code op. Hij kopieert de waarde die bij E gegeven wordt en plakt deze waarde in D2.

Etc..

Dat kan dus inderdaad

[KaiseRRuby]

Ik maak hem als volgt aan nu:

=VERT.ZOEKEN(A2;alles!A:F;3;onwaar)

Dit formule komt dan dus gewoon in B2 te staan. Geen resultaat oid. Ik verwacht eigenlijk dat ik de formule moet aanpassen in C2, D2, E4, F2 om de juiste waarden in totaal te krijgen.

[StecaGrid]

Is het niet mogelijk om via DM de excel file toe te sturen, dan kijk ik er even naar voor je. Uiteraard vertrouwelijk.

[Angeloonie]

KaiseRRuby schreef op maandag 13 juli 2015 @ 12:04:
Ik maak hem als volgt aan nu:

=VERT.ZOEKEN(A2;alles!A:F;3;onwaar)

Dit formule komt dan dus gewoon in B2 te staan. Geen resultaat oid. Ik verwacht eigenlijk dat ik de formule moet aanpassen in C2, D2, E4, F2 om de juiste waarden in totaal te krijgen.

Tsja dan doe je echt iets verkeerd, als je zoekwaarde A2 gewoon in de kolommen A:F voorkomt en er iets in de 3e kolom van bereik A:F (in dit geval makkelijk: kolom C dus) staat, dan geeft hij dit gewoon terug als resultaat. Met jouw formule komt er bij mij in Excel prima resultaat uit. Of zoek je hier toevallig een EAN die simpelweg niet op het tabblad "alles" staat?

Nogmaals:

A2 = je zoekwaarde (je EAN dus)
alles!A:F = je bereik waarnaar je op zoek gaat naar je zoekwaarde (A2)
3 = de kolom in je zoekbereik waaruit je de waarde wilt halen indien je zoekwaarde gevonden is in diezelfde regel
onwaar = EAN moet exact gelijk zijn

[ Voor 21% gewijzigd door Angeloonie op 13-07-2015 13:04 ]

[KrazyJay]

Sowieso zou ik bij deze orde van grootte index/match gebruiken. Dat is veel lichter voor het systeem, naast dat het nog wat andere voordelen heeft.

[KaiseRRuby]

Angeloonie schreef op maandag 13 juli 2015 @ 13:01:
[...]


Tsja dan doe je echt iets verkeerd, als je zoekwaarde A2 gewoon in de kolommen A:F voorkomt en er iets in de 3e kolom van bereik A:F (in dit geval makkelijk: kolom C dus) staat, dan geeft hij dit gewoon terug als resultaat. Met jouw formule komt er bij mij in Excel prima resultaat uit. Of zoek je hier toevallig een EAN die simpelweg niet op het tabblad "alles" staat?

Nogmaals:

A2 = je zoekwaarde (je EAN dus)
alles!A:F = je bereik waarnaar je op zoek gaat naar je zoekwaarde (A2)
3 = de kolom in je zoekbereik waaruit je de waarde wilt halen indien je zoekwaarde gevonden is in diezelfde regel
onwaar = EAN moet exact gelijk zijn

Nee, heb de controle gedaan en de EAN staat er gewoon tussen. Vind het zelf ook onbegrijpelijk.

[Angeloonie]

Stuur je Excel anders even op, dan kijk ik er even naar.

[Reinier]

Maak anders een macro die de benodigde regels uit alles naar specifiek kopieert als je VERT.ZOEKEN niet voor elkaar krijgt

[Icephase]

KaiseRRuby schreef op maandag 13 juli 2015 @ 13:16:
[...]


Nee, heb de controle gedaan en de EAN staat er gewoon tussen. Vind het zelf ook onbegrijpelijk.

Let op dat je je celopmaak in beide tabellen gelijk hebt! Een EAN als tekst is voor Excel niet hetzelfde als een EAN als getal! Ik denk dat dat ook de reden is waarom je formule niet werkt, die zul je ws. als tekst hebben opgemaakt...

[StecaGrid]

ohja, vergeet ook niet de EAN-codes alfabetisch te sorteren.. dat kan pijnlijke foutjes voorkomen.

[KaiseRRuby]

Icephase schreef op maandag 13 juli 2015 @ 13:20:
[...]


Let op dat je je celopmaak in beide tabellen gelijk hebt! Een EAN als tekst is voor Excel niet hetzelfde als een EAN als getal! Ik denk dat dat ook de reden is waarom je formule niet werkt, die zul je ws. als tekst hebben opgemaakt...

Dit was exact het probleem! Beide op Algemeen gezet en dat was de oplossing.

Dank allen!

[Icephase]

Super dat het gelukt is!

rik11 schreef op maandag 13 juli 2015 @ 13:43:
ohja, vergeet ook niet de EAN-codes alfabetisch te sorteren.. dat kan pijnlijke foutjes voorkomen.

Deze opmerking snap ik niet, rik11? Wil je dit eens uitleggen? Het mooie van verticaal zoeken is juist dat je niet hoeft te sorteren...

[StecaGrid]

Icephase schreef op woensdag 15 juli 2015 @ 01:38:
Super dat het gelukt is!


[...]


Deze opmerking snap ik niet, rik11? Wil je dit eens uitleggen? Het mooie van verticaal zoeken is juist dat je niet hoeft te sorteren...

Nou, ik heb er wel eens fouten door zien komen. Sindsdien sorteer ik altijd

[Saffie_time]

Ja kan ook in A =tekst(B2;0) (B2 is in dit geval opzoekwaarde) zetten.
Dit doe je in beide bladen, dus in kolom A een =tekst(opzoekwaarde;0).
Dan normaal je vert.zoeken invoeren vanuit A te zoeken/vergelijken.
Works every time.

[kgb545]

Waarom doe je dit trouwens omslachtig met VLOOKUP? Als je de EAN lijst hebt, kan je toch ook een advanced filter gebruiken? de "Alles" lijst als bron, en de 800EAN lijst als criteria. Het enige waar je op moet letten is dat de kolomtitels gelijk zijn (bijvoorbeeld allebei EAN).

[Yukkie]

Je zou het ook zo op kunnen lossen:

code:

=VLOOKUP([@EAN];Table1;2;FALSE)

hij zoekt dan de waarde uit je kolom EAN van je tabel op sheet 'specifiek' op en vergelijkt die met Table1 (als de tabel op je sheet 'alles' zo heet), pakt dan de waarde uit kolom 2 (in het voorbeeld van thefal is dat de naam van het product). Voorwaarde is dan wel dat je je data als tabel heb geformatteerd, maar dat zou je tegenwoordig standaard moeten doen vind ik

Het voordeel is dat je je tabellen kunt verplaatsen naar andere sheets, of je kolommen in table2 (op 'Specifiek') kunt shuffelen, zonder dat het invloed heeft op je Vlookup-je.

KrazyJay schreef op maandag 13 juli 2015 @ 13:15:
Sowieso zou ik bij deze orde van grootte index/match gebruiken. Dat is veel lichter voor het systeem, naast dat het nog wat andere voordelen heeft.

Kun je wellicht uitleggen wat voor voordelen dit heeft:

code:

=INDEX(Table1[Price];MATCH([@EAN];Table1[EAN];0))

Ten opzichte van een Vlookup zoals hierboven? Ik zie het nog niet helemaal namelijk. Ik vind het vooral een omslachtige wijze om Vlookup te schijven

Edit: ah, nou zie ik het! je lookup value hoeft niet perse in de eerste kolom te staan! Dat is wel erg relaxt! thanks voor deze tip

[ Voor 52% gewijzigd door Yukkie op 15-07-2015 14:07 ]

[c0burn]

Ik kende INDEX/MATCH ook niet, vond deze link (Engels) :
http://www.mbaexcel.com/e...h-is-better-than-vlookup/

Wellicht tijd om te switchen dus

[Icephase]

rik11 schreef op woensdag 15 juli 2015 @ 07:26:
[...]

Nou, ik heb er wel eens fouten door zien komen. Sindsdien sorteer ik altijd

Misschien komt dat wel omdat je geen 'false' of 'onwaar' in je vert.zoeken functie hebt gebruikt. Is soms wel nodig!

Nog een nadeel aan vert.zoeken is dat er geen dubbelingen voor mogen komen. Hoe dat zit met MATCH en INDEX durf ik niet te zeggen, maar dat kan eventueel ook nog een reden zijn om daarvoor te kiezen.