Formule voor opzoeken in matrix

[Maik_nl]

Voor het berekenen van een juiste samenstelling heb ik een matrix gemaakt:



Een aantal gegevens zijn bekend, namelijk:
Cel A1 gebouwhoogte (bijvoorbeeld 11 meter hoog);
CEL A2 oppervlakte van glas (bijvoorbeeld 3,13 m2).

In de matrix moet opgezocht worden welke samenstelling voldoet (RIJ 1, KOLOM C t/m M)

In dit voorbeeld zou dit betekenen dat de groene cel voldoet aan de criteria en zou ik dus graag als 'antwoord' de waarde 6/4 zien.

Welke formule kan hiervoor het best gebruikt worden? Alvast hartelijk dank voor de hulp!

[Dirk]

Zoeken in tabellen doe je typisch met XLOOKUP (in het Nederlands X.ZOEKEN)
In dit geval gebruik je xlookup twee keer, genest. Eerst wil je een hele rij uit de tabel C2:J22 laten teruggeven op basis van de zoekwaarde in A2:22, daarna wil je zoeken in de gevonden rij om een waarde uit C3:J3 te selecteren. Let op dat je wil zoeken op de eerstvolgende grotere waarde, niet op een exacte overeenkomst.

[SjoerdOe]

Even Googlen op INDEX-MATCH geeft je de antwoorden die je zoekt.

[Maik_nl]

Ik heb nu de volgende formule gemaakt:

=X.ZOEKEN(G14;INDIRECT(K14&"!A2:A24");INDIRECT(K14&"!C2:M24");;1)

Met INDIRECT wordt gezocht naar het juiste tabblad omdat deze variabel is. Op dit tabblad staat de tabel welke hierboven al staat afgebeeld.

Nu krijg ik met de uitkomst van rij 4 (CEL C t/m M). Maar de 2e variabel/ formule waarin het oppervlakte wordt bepaald (3,33) is nog niet zichtbaar. De uitkomst zou uiteindelijk de KOLOMNAAM moeten zijn. In dit geval dus 6/4 wat staat voor een bepaalde samenstelling van glas in dit geval. Maar hoe kan ik dat aanvullen in de formule? Of zijn hiervoor meerdere (hulp)formules nodig?

[dixet]

Zo'n complexe formule kan je altijd beter opsplitsen in een paar stappen in aparte cellen, dan zie je ook waar het eventueel fout gaat. Dan kan je het als alles werkt het altijd nog in 1 formule combineren.

Je gebruik nu X.ZOEKEN. Dat geeft een lijst met waardes terug uit de juiste rij. Wat je wilt is het kolomnummer zodat je die kan gebruiken in een INDEX formule om de waarde uit rij 1 op te halen.

Het resultaat van je X.ZOEKEN formule kan je gebruiken in de MATCH functie om de kolom te vinden waar 3,33 in staat.

Dat kolomnummer kan je weer gebruiken in een INDIRECT functie om de waarde uit rij 1 op te halen

code:

=INDIRECT ( ADDRESS ( row , col ) )

[Dirk]

@dixet, waarom zou je het jezelf zo moeilijk maken? X.zoeken is juist geïntroduceerd om al dat gedoe met VERGELIJKEN en INDEX te elimineren.

Het is dus simpelweg:

code:

=X.ZOEKEN(<Zoekwaarde>;<Vorig resultaat>;INDIRECT(K14&"!C1:M1");;1)

Waarbij je voor <Vorig resultaat> zowel kunt verwijzen naar de cellen waar dit in staat als gewoon de vorige formule kunt invoegen.

[ Voor 3% gewijzigd door Dirk op 13-01-2025 19:34 ]

[Maik_nl]

@Dirk

Maar werkt dit ook met twee zoekwaardes? Het zijn namelijk 2 variabelen. De gebouwhoogte (in dit voorbeeld 11, dus eerst volgende waarde in tabel is 12) en het oppervlakte (in dit voorbeeld 3,13 m2, dus de eerst volgende waarde uit de tabel 3,33 omdat voorgaande waarde te klein is en dus niet voldoet).

Deze twee variabelen geeft als resultaat de kolomnaam C1:M1

[Dirk]

Ja, je voert X.zoeken gewoon twee keer uit, met twee verschillende zoekwaardes.
De formule komt in dit geval op het volgende neer:

code:

=X.ZOEKEN(<Zoekwaarde kolommen>;X.ZOEKEN(<Zoekwaarde rijen>;<Rijhoofden>;<Zoektabel>;;1);<Kolomhoofden>;;1)