[Excel] Zoeken in een tabel met ranges

[Patrick_6369]

Ik heb een tabel die ziet er als volgt uit:

code:

Van         Tot         Naam
1           12          Jan
16          20          Piet
25          36          Katrien

Ik wil nu in een lijst met getallen de naam er achter zetten. Dus het resultaat moet zoals hieronder zijn (edit: de eerste kolom is een gegeven, de tweede kolom het resultaat van de formule):

code:

3      Jan
5      Jan
14     #N/A
30     Katrien

Als een getal dus niet voorkomt in de lijst, dan is het prima dat er een foutmelding komt. Deze gevallen wil ik er op deze manier ook uit filteren en mijn bronbestand gaan verbeteren. Dat is nu een zootje van een paar duizend regels.

Mijn probleem: alle oplossingen die ik gevonden heb gaan uit van een bron waarbij de 'van' van de tweede regel direct aasluit bij de 'tot' van de eerste regel. Feitelijk gewoon een VLOOKUP maar dan met True op het eind ipv FALSE, dus geen ecact matches toestaan. Het moet mogelijk zijn, maar ik heb gewoon geen enkel idee in welke richting ik het moet zoeken. Wie duwt me in de goede richting? Wie heeft er al de juiste term om op te googlen?

Ik gebruik Excel Professional Plus 2016 (die stelt mijn baas ter beschikking).

[ Voor 7% gewijzigd door Patrick_6369 op 27-06-2022 11:20 ]

[Tazzios]

In de eerste tabel naam als eerste kolom zetten.

in de 2e tabel 3 kolommen toevoegen.
met vertzoeken 'van' waarde ophalen
vertzoeken 'tot' waarde ophalen
formule die het eerste getal vergelijk met de 'van' en 'tot' en een ja of nee terug geeft.

[ Voor 7% gewijzigd door Tazzios op 27-06-2022 10:00 ]

[Patrick_6369]

Tazzios schreef op maandag 27 juni 2022 @ 09:58:
met vertzoeken 'van' waarde ophalen
vertzoeken 'tot' waarde ophalen

Dank je voor je reactie. Op basis van wat had jij bedacht dat ik deze waardes opzocht?
Toch niet op basis van de naam? Want die is nog onbekend?

De tweede blok in mijn OP is de gewenste output, dus de kolom met de namen is waarvoor ik de formule zoek.

[KabouterSuper]

En als je een countifs toevoegt waarmee je checkt of je waarde uberhaubt in een van/tot-range ligt?

[Tazzios]

Dan begreep ik heb verkeerd. Gebruik bij vert.zoeken de 'benaderen' optie.

=VERT.ZOEKEN(A8;Tabel1;3;1)

[ Voor 6% gewijzigd door Tazzios op 27-06-2022 11:31 ]

[Reptile209]

Tazzios schreef op maandag 27 juni 2022 @ 11:29:
Dan begreep ik heb verkeerd. Gebruik vert.zoeken ' benaderen'

=VERT.ZOEKEN(A8;Tabel1;3;1)

Daarmee haal je de case van 14 er niet uit: die wordt dan aan Jan toegewezen, omdat Piet pas bij 16 begint. VERT.ZOEKEN kijkt alleen naar de ondergrens, en dan naar de ondergrens van de volgende in de tabel. De kolom met de bovengrens neemt hij niet mee.

Je kan er nog een hulpkolom naast zetten in je resultaten, waar je de check doet via een VERT.ZOEKEN op de naam, en dan de bovengrens vergelijkt met de gevraagde waarde.

Of, mogelijk nog makkelijker, zorgen dat je lookup-table 'compleet' is. Dus op het handje zorgen dat er - in dit makkelijke voorbeeld - een regel tussen Jan en Piet komt met "12 - 16 - ONBEKEND" ofzo. Maak desnoods even een check-kolom bij de lookup die de Tot-waarde vergelijkt met de Van-waarde op de volgende regel. Als die niet matchen, moet je daar wat invoegen.

[KabouterSuper]

KabouterSuper schreef op maandag 27 juni 2022 @ 11:28:
En als je een countifs toevoegt waarmee je checkt of je waarde uberhaubt in een van/tot-range ligt?

Dus een simpele vlookup

code:

VLOOKUP(waarde,A:C,3,false)

vervangen door

code:

if(COUNTIFS(A:A,"<=waarde",A:A,">=waarde")>0 ,VLOOKUP(waarde,A:C,3,false),"#N/A")

Update voor het nageslacht: de false in de formules moet natuurlijk een true zijn.

[ Voor 47% gewijzigd door KabouterSuper op 27-06-2022 14:06 ]

[Patrick_6369]

De oplossing van @KabouterSuper lijkt in het voorbeeld goed te werken, ik ga nu het totaalbestand in om het toe te passen! EDIT:

Ik heb de formule iets moeten aanpassen naar:

code:

=IF(COUNTIFS(A:A;"<="&F2;B:B;">="&F2)>0;VLOOKUP(F2;A:C;3;TRUE);"#N/A")

Aanpassing zit in de tweede reeks A:A uit formule van KabouterSuper, dat moet B:B zijn en de VLOOKUP moet op TRUE eindigen.

Reptile209 schreef op maandag 27 juni 2022 @ 11:34:
[...]Of, mogelijk nog makkelijker, zorgen dat je lookup-table 'compleet' is. Dus op het handje zorgen dat er - in dit makkelijke voorbeeld - een regel tussen Jan en Piet komt met "12 - 16 - ONBEKEND" ofzo.

Helaas is dat te omslachtig. Het gaat om een bestand met duizenden regels. Het betreffen codes met 10 cijfers en niet alle codes zijn in gebruik.

[ Voor 57% gewijzigd door Patrick_6369 op 27-06-2022 14:01 ]

[KabouterSuper]

Patrick_6369 schreef op maandag 27 juni 2022 @ 12:58:
De oplossing van @KabouterSuper lijkt in het voorbeeld goed te werken, ik ga nu het totaalbestand in om het toe te passen! EDIT:

Ik heb de formule iets moeten aanpassen naar:

code:

=IF(COUNTIFS(A:A;"<="&F2;B:B;">="&F2)>0;VLOOKUP(F2;A:C;3;TRUE);"#N/A")

Aanpassing zit in de tweede reeks A:A uit formule van KabouterSuper, dat moet B:B zijn en de VLOOKUP moet op TRUE eindigen.

Ik was iets te slordig met de formule, maar top dat je er er zelf uitgekomen bent. Je kunt voor de gein de countifs>0 even veranderen in countifs=1 om te controleren dat je waarden niet per ongeluk in twee ranges zitten.