Vert.zoeken maar dan anders

[Withaar]

Ik zoek een formule om de eerste cel te vinden waar een waarde afwijkt.
Dus iets van =vert.zoeken(<>1000;A1:C64000;3)

Dus vind in een kolom de eerste waarde die niet gelijk aan 1000 is en geef me dan de waarde in die rij, uit een andere kolom.

 

[madmuppet]

Om te beginnen.... je moet dan ook horizontaal.zoeken gebruiken.
Laat je niet foppen door het bereik van A1 t/m C64000 Je hebt weliswaar een verticaal bereik (regel 1 t/m 64000) maar binnen dat bereik (dus in iedere regel) zoek je uiteindelijk horizontaal. Je checkt immers een afwijkende waarde in kolom A, waarna je een waarde uit dezelfde regel uit kolom C wilt zien.
Deze formule (<>1000;A1;C64000;3) lijkt natuurlijk nergens op, maar ik snap wat je bedoelt.

Maar...... waarom niet gewoon ALS gebruiken?
=ALS($A1<>1000;$C1;A1) dus:check waarde in A1 of deze afwijkt, van 1000, zoja, geef waarde uit C1, zoniet, geef originele waarde uit A1)

 

[Withaar]

Ik zie niet in wat het nut is om horiz.zoeken te gebruiken, dat gaat ook niet werken.

Het probleem is dat zowel vert.zoeken als horiz.zoeken, zoeken naar een waarde, bv 1000.
Wat ik zoek is de (eerste) rij die afwijkt van de gezochte waarde en dan een waarde uit de zelfde rij maar van een andere kolom.

In cel A1 staat 1000, in cel A2 staat 1000, dit gaat door tot cel Ax waar bijvoorbeeld 500 in staat, wat ik nu zoek is de waarde Cx.
N.b. het hele blad veranderd regelmatig, een soort 'algemene formule' zou dus zeer welkom zijn.

Je ALS formule zou kunnen, ik heb dan echter een extra kolom nodig om elke A waarde te controlene en de Cx waarde krijg ik niet in de cel waar ik de waarde in zou willen hebben. Niet echt een oplossing dus.

Zelf zat ik te denken aan het laten zoeken naar de 1 na grootste waarde in de range, daarvan de rij uitzoeken en dan naar een bepaalde kolom kijken. Maar dat wil echter nog niet echt lukken.

N.b. De optie 'benaderen' kun je ook niet gebruiken, dit werkt alleen bij een gesorteerde tabel A-Z, wat ik heb is wel gesorteerd, maar Z-A.

 

[madmuppet]

Horizontaal zoeken gaat het probleem ook niet oplossen denk ik. Tot die conclusie was ik ook al gekomen.

Hoe dan ook, zal je elke controleformule in een extra kolom of cel moeten plaatsen. Maakt niet uit of je dan horizontaal of verticaal gaat zoeken, een ALS formule gebruikt, of desnoods GELIJK of iets dergelijks gaat gebruiken.

Maar aan de andere kant... is dat echt zo erg? Er is ruimte zat. Je hebt 255 bladen, met 65.563 rijen en 256 kolommen ter beschikking. Da's meer dan 16,7 miljoen cellen per blad. Alles kan, maar het lijkt me dat je dat niet zomaar vol hebt.
Als je even over nadenkt hoe dat het beste in te passen valt, moet daar wel een plaatsje voor vinden zijn in je spreadsheet. Desnoods zet je het hele ding op een ander blad.

Als je echt onder die extra kolom uitwilt, verval je in VBA ben ik bang. Post anders even een voorbeeldje

 

[Withaar]

Ik kan je niet volgen, als ik vert.zoeken gebruik kan ik met 1 formule, in 1 cel een gezochte waarde vinden en gebruiken. Snel en simpel.
Met =ALS langs een hele kolom, en dan? Hoe krijg ik gezochte waarde uit kolom C dan een een bepaalde cel?

Probleem met een formule als =VERT.ZOEKEN(KLEINSTE(A:A;2);A:C;3) is dat vert.zoeken alleen werkt in een A-Z gesorteerde tabel.

Voorbeeldje bijgesloten.

 

[richard1970]

Beste Withaar,

Ben het met Madmuppet eens. Je zoekt geen waarde. Je zoekt juist een waarde niet. De eenvoudigste oplossing is via een hulpkolom. Bij voorkeur kolom A zodat je vert.zoeken kunt gebruiken. Op iedere gebruikte regel moet dan iets komen te staan als:
=als(B2<>$A$1;"ja";"nee"). Waarbij cel A1 staat voor de waarde waaraan de waarde in kolom B niet aan moet voldoen.
Vervolgens gebruik je elders =vert.zoeken(A2:C64000;"ja";3;onwaar) om de eerste waarde uit kolom C te vinden waarbij op dezelfde rij de waarde in kolom B niet gelijk is aan de waarde in cel A1.

succes,

Richard

 

[Withaar]

Beste Withaar,

Ben het met Madmuppet eens. Je zoekt geen waarde. Je zoekt juist een waarde niet.

Dat ik een niet waarde zocht was mij ook wel duidelijk.

Maar je antwoord heeft me wel in de goede richting gestuurd.
N.b. je antwoord werkt niet, probeer het maar eens

Probleem is ook dit keer weer de A-Z sortering (lijkt het, of ik heb ergens ander een fout gemaakt)...
Als je jou voorbeeld formules echter omdraait werken ze wel, dus;
=als(B2<>$A$1;"nee";"je")
en
=vert.zoeken(A2:C64000;"nee";3;onwaar)

Nou ja, inder geval bedankt.

 

[LucB]

Hallo Withaar,

Deze zoekt het ADRES.

 

[Withaar]

@ Luc, dank voor je reactie, maar dat is niet helemaal wat ik bedoel.
In cel E6 zou ik de formule willen hebben die mij de waarde uit de c kolom geeft die hoort bij de laatste (laagste) waarde uit kolon A.
Dit is toch wat anders.
Vergelijken werkt ook weer met een zoek waarde, niet met een 'niet' zoekwaarde, dus ik zie ook niet zo snel hoe ik deze formule op een andere manier zou kunnen gebruiken.

Met =kleinste(A:A) zou je de cel (de waarde) kunnen vinden, dan zou je die waarde moeten gaan vergelijken... en dan nog iets met adres... en daar dan nog de waarde van.

 

[Withaar]

Met =ADRES(VERGELIJKEN(KLEINSTE(A:A;1);$A$1:$A$1000;0);1)
kan ik de cel-coordinaten van cel vinden met de kleinste waarde in kolom A.
Nu nog de waarde in de C kolom. ( waarde via =INDIRECT`).

Maar de C kolom, iets met verschuiving??

 

[Withaar]

Zo dus;

=INDIRECT(ADRES(VERGELIJKEN(KLEINSTE(A:A;1);A:A;0);3))

Niet helemaal de oplossing van de vraag zoals ik hem gesteld had, maar wel voor mijn probleem op dit moment.

Bedankt allemaal voor het mee denken!

 

[Wigi]

Dag allen

deze mooie formule doet het

=INDEX($A$1:$C$100;VERGELIJKEN(1;--($A$1:$A$100<>1000);0);3)

Dit is een array formule, dus Ctrl-Shift-Enter ipv Enter.

Verander de 100 tot zover je nodig hebt, maar spring niet te roekeloos om met zulke functies. Zeker bij vele rijen kunnen ze traag worden.

Wigi

 

[madmuppet]

Kijk, zo'n voorbeeldje verduidelijkt veel.
Heb niet eerder de gelegheid gehad om deze thread verder te bekijken, maar het is uiteindelijk toch nog een charmante oplossing geworden. :thumb: