Samenvatting
Om een tweeweg-zoekactie bij benadering uit te voeren met meerdere criteria, kunt u een matrixformule gebruiken op basis van INDEX en MATCH, met behulp van de ALS-functie om criteria toe te passen. In het getoonde voorbeeld is de formule in K8:
=INDEX(data,MATCH(K6,IF(material=K5,hardness),1),MATCH(K7,diameter,1))
waarbij gegevens (D6: H16), diameter (D5: H5), materiaal (B6: B16) en hardheid (C6: C16) benoemde bereiken zijn die alleen voor het gemak worden gebruikt.
Opmerking: dit is een matrixformule en moet worden ingevoerd met Control + Shift + Enter
Uitleg
Het doel is om een voedingssnelheid op te zoeken op basis van materiaal, hardheid en boordiameter. Aanzet waarden zijn in de genoemde range data (D6: H16).
Dit kan worden gedaan met een bidirectionele INDEX- en MATCH-formule. De ene MATCH-functie berekent het rijnummer (materiaal en hardheid), en de andere MATCH-functie zoekt het kolomnummer (diameter). De functie INDEX retourneert het uiteindelijke resultaat.
In het getoonde voorbeeld is de formule in K8:
=INDEX(data, MATCH(K6,IF(material=K5,hardness),1), // get row MATCH(K7,diameter,1)) // get column
(Regeleindes alleen toegevoegd voor leesbaarheid).
Het lastige is dat materiaal en hardheid samen moeten worden behandeld. We moeten MATCH beperken tot de hardheidswaarden voor een bepaald materiaal (koolstofarm staal in het getoonde voorbeeld).
We kunnen dit doen met de ALS-functie. In wezen gebruiken we IF om irrelevante waarden "weg te gooien" voordat we naar een overeenkomst zoeken.
Details
De INDEX functie krijgt de benoemde bereik gegevens (D6: H16) als matrix voor. De eerste MATCH-functie berekent het rijnummer:
MATCH(K6,IF(material=K5,hardness),1) // get row num
Om de juiste rij te vinden, moeten we een exacte match maken op materiaal en een geschatte match op hardheid. We doen dit door de ALS-functie te gebruiken om eerst irrelevante hardheid eruit te filteren:
IF(material=K5,hardness) // filter
We testen alle waarden in materiaal (B6: B16) om te zien of ze overeenkomen met de waarde in K5 ("Low Carbon Steel"). Als dit het geval is, wordt de hardheidswaarde doorgerekend. Zo niet, dan geeft IF FALSE terug. Het resultaat is een array als deze:
(FALSE;FALSE;FALSE;85;125;175;225;FALSE;FALSE;FALSE;FALSE)
Merk op dat de enige overgebleven waarden zijn die geassocieerd zijn met koolstofarm staal. De andere waarden zijn nu FALSE. Deze array wordt direct naar de MATCH-functie geretourneerd als de lookup_array.
De opzoekwaarde voor match komt van K6, die de gegeven hardheid 176 bevat. MATCH is geconfigureerd voor een geschatte match door match_type in te stellen op 1. Met deze instellingen negeert MATCH FALSE-waarden en retourneert de positie van een exacte match of de eerstvolgende kleinste waarde .
Opmerking: hardheidswaarden moeten voor elk materiaal in oplopende volgorde worden gesorteerd.
Met de hardheid gegeven als 176, geeft VERGELIJKEN 6 als resultaat, rechtstreeks aan INDEX geleverd als het rijnummer. We kunnen nu de originele formule als volgt herschrijven:
=INDEX(data,6,MATCH(K7,diameter,1))
De tweede MATCH-formule vindt het juiste kolomnummer door een geschatte overeenkomst op diameter uit te voeren:
MATCH(K7,diameter,1) // get column num
Opmerking: waarden in diameter D5: H5 moeten in oplopende volgorde worden gesorteerd.
De opzoekwaarde is afkomstig van K7 (0,75) en de lookup_array is de genoemde bereikdiameter (D5: H5).
Net als eerder wordt de MATCH ingesteld om de overeenkomst bij benadering te benaderen door match_type in te stellen op 1.
Met diameter gegeven als 0,75, geeft VERGELIJKEN 3 als resultaat, rechtstreeks afgeleverd bij de functie INDEX als het kolomnummer. De oorspronkelijke formule is nu opgelost:
=INDEX(data,6,3) // returns 0.015
INDEX retourneert een eindresultaat van 0,015, de waarde van F11.
