(Excel) zoek een formule index vergelijken

[Verwijderd]

goedemiddag,

ik zoek een formule die moet zoeken in verschillende departementen en daarbij het tarief moet weergeven die hoort bij aantal pallets. dus stel ik heb 2 pallets vervoerd naar dept 67 (kolom dept6) dan zou de formule € 183,76 moeten weergeven.

ik moet dus op basis van 2 waardes zoeken, eerst moet ik het departement vinden, daarna het bedrag dat bij aantal pallets hoort.

is dit mogelijk?


Kolom1 dept dept2 dept3 dept4 dept5 dept6
59 2 54 77 27 10
62 8 57 78 51 14
80 60 91 55 28
92 75 45
93 76 52
94 61
95 67
88
89

aantal pallets
1 € 117,03 € 125,73 € 136,37 € 136,37 € 147,98 € 147,98
2 € 145,08 € 156,68 € 166,36 € 168,29 € 180,87 € 183,76
3 € 167,32 € 180,87 € 192,48 € 197,31 € 207,95 € 215,68
4 € 209,88 € 231,16 € 241,80 € 252,44 € 263,07 € 276,62
5 € 240,83 € 265,01 € 279,52 € 294,02 € 305,63 € 324,01
6 € 258,24 € 283,39 € 304,66 € 324,01 € 337,55 € 361,74
7 € 268,88 € 294,03 € 318,21 € 340,46 € 353,99 € 380,11

tabel ziet er niet goed uit maar ben hier voor het eerst.

zie jullie reacties graag tegemoet.

alvast bedankt.

[Niet_Jan_Jaap]

Het lijkt alsof je gewoon een waarde op wil zoeken in een tabel, wat natuurlijk kan. Misschien kan je met een screenshot oid laten zien wat je 'opzoektabel' is en wat je doelsituatie is.

wat je nodig hebt is (Engelse notitie) Index(Match)) of Vlookup(). Deze formules kunnen hetzelfde weergeven, ik heb zelf een sterke voorkeur voor index(Match)).

[Verwijderd]

pallets dept dept2 dept3 dept4 dept5
59 2 54 77 27
62 8 57 78 51
80 60 91 55
92 75
93 76
94
95


1 € 117,03 € 125,73 € 136,37 € 136,37 € 147,98
2 € 145,08 € 156,68 € 166,36 € 168,29 € 180,87
3 € 167,32 € 180,87 € 192,48 € 197,31 € 207,95
4 € 209,88 € 231,16 € 241,80 € 252,44 € 263,07
5 € 240,83 € 265,01 € 279,52 € 294,02 € 305,63
6 € 258,24 € 283,39 € 304,66 € 324,01 € 337,55
7 € 268,88 € 294,03 € 318,21 € 340,46 € 353,99

Ik kan geen screenshot toevoegen?!
ik zal het zo duidelijk mogelijk uitleggen, ik wil een vergelijking maken tussen 2 facturen, ik heb kosten doorberekend gekregen die ik wil vergelijken aan de hand van de tabel boven.
1 tm 7 zijn aantal pallets, dept t/m dept5 zijn de departementen waaruit de tarieven bestaan.
59 is een district komt onder kopje dept, 27 onder dept5 sorry dat het me niet beter lukt om de tabel te laten zien.
stel dat ik district 27 voor 2 pallets ben doorbelast dan moet de formule eerst zoeken in welke departement het voorkomt vervolgens hoeveel pallets het zijn geweest, uitkomst in deze moet zijn € 180,87.
ik kan verticaal zoeken op 1 zoekwaarde, nu moet er eerst gekeken worden welke departement vervolgens hoeveel pallets, vervolgens uitkomst waarde.

hoop dat het een beetje duidelijk is.

ik moet op basis van 2 gegevens zoeken district (BV27) en aantal pallets.

bedankt

[Lustucru]

Het kan, maar de opzet van je dept tabel maakt het veel lastiger dan noodzakelijk. Als je die omzet naar een tabel met twee kolommen (department / tariefgroep 1-6) dan is het daarna simpel.

[Verwijderd]

hoe kan ik een tabel vanuit Excel importeren of zo plakken dat tabel duidelijk is, bovenstaande is niet duidelijk.

[GRDavies75]

Ik ben ook niet zo handig om een 'juist' formaat hier te plaatsen, maar heb wel ontdekt dat de reageer knop iets uitgebreider is dan het Quickreply invoerveld. Daar zie ik ook een tabelknopje, dus misschien de oplossing (of een duw de goede kant op)?

[Lustucru]

Dept	Tarief
59	1
2	2
67	6

Zo dus. Moet je wel handmatig doen met HTML. Dat wijst zichzelf als je op het tabelknopje klikt. Maar dit is iig de wijze waarop je je tarieftabel moet opzetten om e.e.a. relatief simpel te laten werken.

[ Voor 4% gewijzigd door Lustucru op 17-01-2019 11:15 ]

[Reptile209]

Maak voor het gemak even een hulpkolom aan. Daarin bepaal je met index(match()) of vlookup() het juiste departement. Vervolgens kan je met offset() de waarde in de juiste rij/kolom in je "prijs per aantal pallets" tabel opzoeken.

Laat even weten of je een Engelse of Nederlandse versie van Excel gebruikt, dan kunnen mensen je de juiste formulenamen aanleveren

[Lustucru]

Reptile209 schreef op donderdag 17 januari 2019 @ 11:25:
Maak voor het gemak even een hulpkolom aan. Daarin bepaal je met index(match()) of vlookup() het juiste departement.

Dat gaat niet met zijn huidige tabel. Kan wel, maar dan zal de TS zijn heil moeten zoeken in matrixformules als {=MAX(N(A1:F10=dept)*KOLOM(A1:F10))} Ik weet niet of dat een goed idee is.

[Verwijderd]

Lustucru schreef op donderdag 17 januari 2019 @ 11:14:

Dept	Tarief
59	1
2	2
67	6

Zo dus. Moet je wel handmatig doen met HTML. Dat wijst zichzelf als je op het tabelknopje klikt. Maar dit is iig de wijze waarop je je tarieftabel moet opzetten om e.e.a. relatief simpel te laten werken.

maar in een dept komen meerdere districten voor, hoe doe ik dat? Ik vind het best lastig omdat ik de tabel niet goed kan laten zien.

[loeberce]

Kan je aangeven hoe je vanaf de eerste tabel met departementen aan de waarde in de 2e tabel komt?

[Verwijderd]

loeberce schreef op vrijdag 18 januari 2019 @ 10:15:
Kan je aangeven hoe je vanaf de eerste tabel met departementen aan de waarde in de 2e tabel komt?

daar zoek ik dus een formule voor, bovenstaande is 1 tabel bestaand uit departementen die verschillende districten bevatten, ik zoek een formule waarbij ik bv een gegeven heb een district bv. 93 en aantal pallets bv 3, dan moet de uitkomst € 167,32 zijn. het is 1 tabel maar ik krijg het niet duidelijk zichtbaar.

[loeberce]

Pallets/dept	21	67
1	€111,25	€134,25
2	€123,25	€156,75

Heb je dan een tabel met departementen als kolomkop en aantal pallets als rijkop, zoals hierboven? Of zijn er binnen verschillende districten departementen met hetzelfde nummer?

[Lustucru]

Verwijderd schreef op vrijdag 18 januari 2019 @ 10:11:
[...]maar in een dept komen meerdere districten voor, hoe doe ik dat? Ik vind het best lastig omdat ik de tabel niet goed kan laten zien.

Whatever, potato, potato

District	Dept
59	1
62	1
80	1
2	2
67	6

Wat je nodig hebt is een opzoektabel van district naar dept (als ik het goed begrijp uit jouw termen), in ieder geval van 'iets' naar een tariefgroep. Het makkelijkste is dat door je tabel om te vormen naa rbovenstaand voorbeeld. Als je uitgaat van een tabel met één kolom per dept, dan moet je gaan werken met een matrixformule als in Lustucru in "(Excel) zoek een formule index vergelijken"

[ Voor 61% gewijzigd door Lustucru op 18-01-2019 11:45 ]

[Verwijderd]

loeberce schreef op vrijdag 18 januari 2019 @ 10:48:

Pallets/dept	21	67
1	€111,25	€134,25
2	€123,25	€156,75

Heb je dan een tabel met departementen als kolomkop en aantal pallets als rijkop, zoals hierboven? Of zijn er binnen verschillende districten departementen met hetzelfde nummer?

Klopt, alleen het probleem is dat er meerdere districten in een departement voorkomen, dus in de kolom met 21 kunnen nog meerdere districten voorkomen.

[Verwijderd]

Lustucru schreef op vrijdag 18 januari 2019 @ 11:19:
[...]


Whatever, potato, potato

District	Dept
59	1
62	1
80	1
2	2
67	6

Wat je nodig hebt is een opzoektabel van district naar dept (als ik het goed begrijp uit jouw termen), in ieder geval van 'iets' naar een tariefgroep. Het makkelijkste is dat door je tabel om te vormen naa rbovenstaand voorbeeld. Als je uitgaat van een tabel met één kolom per dept, dan moet je gaan werken met een matrixformule als in Lustucru in "(Excel) zoek een formule index vergelijken"

ja onder andere, als ik weet in welke district dan moet ik aan de hand van het aantal pallets het tarief bepalen. zoals ik al zei bestaat een departement uit meerdere districten. ik heb 95 districten verdeeld over 20 departementen.

[Icephase]

Je kunt een unieke combi maken van dept en district. Je krijgt dan een aantal nieuwe 'ID'-nummers. Die kun je in een tabel zetten met het aantal pallets en de tarieven. Dan kun je wel zoeken. Eerst dus een hulptabel maken die van het dept-nr en het district-nr een nieuwe 'ID' maakt.

[Verwijderd]

Icephase schreef op vrijdag 18 januari 2019 @ 12:24:
Je kunt een unieke combi maken van dept en district. Je krijgt dan een aantal nieuwe 'ID'-nummers. Die kun je in een tabel zetten met het aantal pallets en de tarieven. Dan kun je wel zoeken. Eerst dus een hulptabel maken die van het dept-nr en het district-nr een nieuwe 'ID' maakt.

is daar een formule voor? of hoe komt het er dan uit te zien? ik moet 95 districten koppelen aan een departement.

[GRDavies75]

Je kan teksten aan elkaar plakken met &, bijvoorbeeld
=A1&B1
of
=A1&"eraangeplakt"

[loeberce]

Je beantwoordt mijn vraag over unieke districten niet. Als district 24 niet in verschillende departement voor kan komen, is zoeken op departement-nummer niet nodig.

[Icephase]

Verwijderd schreef op vrijdag 18 januari 2019 @ 12:26:
[...]


is daar een formule voor? of hoe komt het er dan uit te zien? ik moet 95 districten koppelen aan een departement.

JA daar is een formule voor... A1&B1 bijvoorbeeld... maar als je dat nog niet weet vraag ik me af of jij wel de beste persoon in jullie bedrijf bent om dit klusje te klaren...?

[Verwijderd]

loeberce schreef op vrijdag 18 januari 2019 @ 12:29:
Je beantwoordt mijn vraag over unieke districten niet. Als district 24 niet in verschillende departement voor kan komen, is zoeken op departement-nummer niet nodig.

ieder district komt maar in 1 departement voor.

[Verwijderd]

Icephase schreef op vrijdag 18 januari 2019 @ 12:29:
[...]


JA daar is een formule voor... A1&B1 bijvoorbeeld... maar als je dat nog niet weet vraag ik me af of jij wel de beste persoon in jullie bedrijf bent om dit klusje te klaren...?

hahahaahahahahaha... je hebt helemaal gelijk.

[loeberce]

Dan kan je toch gewoon op districtsnummer zoeken? Want dan is het departementnummer niet meer relevant.

[Verwijderd]

loeberce schreef op vrijdag 18 januari 2019 @ 13:04:
Dan kan je toch gewoon op districtsnummer zoeken? Want dan is het departementnummer niet meer relevant.

klopt, ik heb 2 gegevens, district en aantal pallets. met deze gegevens moet ik het bedrag dat erbij hoort vinden.

[loeberce]

Verwijderd schreef op vrijdag 18 januari 2019 @ 13:07:
[...]


klopt, ik heb 2 gegevens, district en aantal pallets. met deze gegevens moet ik het bedrag dat erbij hoort vinden.

Kijk dan eens bij deze link.

[Verwijderd]

loeberce schreef op vrijdag 18 januari 2019 @ 13:08:
[...]

Kijk dan eens bij deze link.

dank je! ik kijk meteen, kom ik er niet uit hoor je nog!

[Lustucru]

Verwijderd schreef op vrijdag 18 januari 2019 @ 12:19:
[...]


ja onder andere, als ik weet in welke district dan moet ik aan de hand van het aantal pallets het tarief bepalen. zoals ik al zei bestaat een departement uit meerdere districten. ik heb 95 districten verdeeld over 20 departementen.

Je valt in herhaling, behalve dan dat het aantal departementen opeens van 6 naar 20 gaat, maar je leest niet. Het antwoord is inmiddels al een paar keer gegeven:je moet zelf die districts-departement tabel overtikken naar één lange tabel met twee kolommen, of je gebruikt een matrixformule.

[Verwijderd]

loeberce schreef op vrijdag 18 januari 2019 @ 13:08:
[...]

Kijk dan eens bij deze link.

=INDEX(B11:U40;match(A44;A11:A40;1);match(A45;B2:U10;1))

krijg als uitkomst #naam?

[loeberce]

Nederlandse Excel of UK?

En in de zoekfuncties moet je alleen in de kolom/rij-koppen zoeken. Dus volgens mij moet bereik B10:U10 in het laatste deel van de formule.

[ Voor 74% gewijzigd door loeberce op 18-01-2019 13:50 ]

[Verwijderd]

loeberce schreef op vrijdag 18 januari 2019 @ 13:48:
Nederlandse Excel of UK?

En in de zoekfuncties moet je alleen in de kolom/rij-koppen zoeken. Dus volgens mij moet bereik B10:U10 in het laatste deel van de formule.

nederlands

[loeberce]

Dan moet je voor MATCH de functie VERGELIJKEN gebruiken.

Die opmerking over bereik uit mijn vorige reactie mag je negeren. Ik zie dat jouw data in meerdere rijen staat.

[ Voor 51% gewijzigd door loeberce op 18-01-2019 13:54 ]

[Verwijderd]

loeberce schreef op vrijdag 18 januari 2019 @ 13:53:
Dan moet je voor MATCH de functie VERGELIJKEN gebruiken.

Die opmerking over bereik uit mijn vorige reactie mag je negeren. Ik zie dat jouw data in meerdere rijen staat.

=INDEX(B11:U40;VERGELIJKEN(A44;A11:A40;0);VERGELIJKEN(A45;B2:U10;0))

uitkomst #N/B terwijl het € 354,00 m/z

[Verwijderd]

loeberce schreef op vrijdag 18 januari 2019 @ 13:53:
Dan moet je voor MATCH de functie VERGELIJKEN gebruiken.

Die opmerking over bereik uit mijn vorige reactie mag je negeren. Ik zie dat jouw data in meerdere rijen staat.

fout zit volgens mij in zoeken in B2:U10

[loeberce]

Dat kan ik vanaf hier niet zien. Gebruik de "formule evalueren" functie eens. Die doorloopt de formule stap voor stap.

[Lustucru]

Verwijderd schreef op vrijdag 18 januari 2019 @ 13:58:
[...]
VERGELIJKEN(A45;B2:U10;0))

Werkt niet. Vergelijken kan niet in een bereik over meerdere rijen en meerdere kolommen. Maar ik val in herhaling.

Verwijderd schreef op vrijdag 18 januari 2019 @ 14:05:
[...]
fout zit volgens mij in zoeken in B2:U10

Exact.

[ Voor 24% gewijzigd door Lustucru op 18-01-2019 14:13 ]

[Verwijderd]

Lustucru schreef op vrijdag 18 januari 2019 @ 14:11:
[...]

Werkt niet. Vergelijken kan niet in een bereik over meerdere rijen en meerdere kolommen. Maar ik val in herhaling.


[...]

Exact.

welke formule wel dan?

[Reptile209]

Verwijderd schreef op vrijdag 18 januari 2019 @ 14:14:
[...]


welke formule wel dan?

Voor zover ik weet: geen. Daarvoor kan je zelf een macro schrijven, of gewoon je data in een 2-kolom tabel zetten. Dat is 1x wat knip en plakwerk, maar daar heb je verder alleen maar lol van.

[Lustucru]

Verwijderd schreef op vrijdag 18 januari 2019 @ 14:14:
[...]


welke formule wel dan?

Die is pas drie keer genoemd... of je doet wat @Reptile209 hierboven zegt.

[ Voor 11% gewijzigd door Lustucru op 18-01-2019 14:44 ]

[Verwijderd]

Ok, ik begrijp en zie het niet, hoe kan ik een 2 kolommen tabel maken van een tabel met 20 kolommen (waar nog eens 95 districten zijn onderverdeeld waarop ik moet zoeken) en 33 rijen ( waar ook op gezocht moet worden), waarschijnlijk val ik weer in herhaling...

[Lustucru]

1: Maak een kopie.
2: Knip de waarden uit kolom 2 en plak die onder kolom 1
3: Knip de waarden uit kolom 3 en plak die onder de data van kolom2 onder kolom 1
4... herhaal dit 20x.
5: Zorg ervoor dat achter de nieuwe kolom de juiste cijfers 1`t/m 20 komen te staan.

van

Dept1	Dept2	Dept3
59	21	67
62		48
80

naar:

District	Dept
59	1
62	1
80	1
21	2
67	3
48	3

[loeberce]

Maar dan is hij de link met de prijzen per pallet kwijt. Een somproduct-formule om het kolomnummer op te halen uit de kolomkoppen op basis van de gewenste waarde is dan de oplossing.

code:

=INDEX(B11:U40;MATCH(A44;A11:A40;0);SUMPRODUCT(COLUMN($B$2:$G$10)*($B$2:$G$10=A45))-1)

Alleen de formule nog even omzetten naar NL met SOMPRODUCT, KOLOM en VERGELIJKEN

[Lustucru]

loeberce schreef op zaterdag 19 januari 2019 @ 14:03:
Maar dan is hij de link met de prijzen per pallet kwijt.

Het is alleen het eerste deel. hé.
Je somproduct formulering doet niets anders dan de eerder genoemde matrixformule alleen met een iets andere syntax. Het is ook een tikje oneigenlijk gebruik van somproduct, omdat je maar één matrix opbouwt. Een correcte syntax met twee matrices werkt werkt niet omdat kolom() by design maar één keer de kolomnummers teruggeeft bij een bereik over meer dan 1 rij.

kolom(A1:C3)={1/2/3} en niet zoals je zou verwachten {1/2/3;1/2/3;1/2/3}

[loeberce]

Lustucru schreef op zaterdag 19 januari 2019 @ 14:30:
[...]

Het is alleen het eerste deel. hé.
Je somproduct formulering doet niets anders dan de eerder genoemde matrixformule alleen met een iets andere syntax. Het is ook een tikje oneigenlijk gebruik van somproduct, omdat je maar één matrix opbouwt. Een correcte syntax met twee matrices werkt werkt niet omdat kolom() by design maar één keer de kolomnummers teruggeeft bij een bereik over meer dan 1 rij.

kolom(A1:C3)={1/2/3} en niet zoals je zou verwachten {1/2/3;1/2/3;1/2/3}

Ik snap je argument niet? Met mijn formule werkt het, zonder matrix formule en zonder aanpassing van de tabel. Dat het anders of beter kan ontken ik niet, maar zo is de TS toch geholpen?