Udvidet Lopslag evt. via VBA

Med dine data i A1:B8 og opslagsværdien i C1, prøv følgende matriksformel i D1:
=INDEX($B$1:$B$8;SMALL(IF($A$1:$A$8=$C$1;ROW($A$1:$A$8));ROW(1:1)))
Kopier formlen nedad til D8.
Hvis du bruger en dansk Excel så oversæt INDEX, SMALL, IF og ROW til INDEKS, MINDSTE, HVIS og RÆKKKE. Formlen returnerer #NUM! når der ikke er flere udgaver af opslagsværdien. Hvis disse #NUM! forstyrrer meget er det muligt, blandt andet ved hjælp af mere formelgymnastik, at fjerne dem.

Husk at matriksformler skal indtastes ved at holde Ctrl og Shift nede før du trykker på Enter.

Jeg har prøvet at rette lidt i formlen men den returnere de forkerte værdier.
Tallene jeg står op ligger i arket liste. men værdierne den skal returnere ligger i et andet ark!
Den skal kunne finde op til 16 værdier som skal returneres men skriver jeg f.eks. 1 finder den også værdier som ligger under 2 og 3!

=INDEKS(liste!B4:B450;MINDSTE(HVIS(liste!A4:A450=$C$1;RÆKKE(liste!A4:A450));RÆKKE(liste!1:1)))

Hvad kan fejlen skyldes?

=INDEKS(liste!$B$4:$B$450;MINDSTE(HVIS(liste!$A$4:$A$450=$C$1;RÆKKE(liste!$A$4:$A$450)-RÆKKE($3:$3));RÆKKE(liste!1:1))). Stadigvæk matriksformel.

Som jeg skrev forudsatte jeg, at dine data startede i A1. Du indikerer så nu, at de starter i A4, derfor tilføjelsen: -RÆKKE($3:$3).
Endelig så husk når du "retter lidt i formlen", så kan du ikke bare se bort fra de dollartegn der fremgik af min formel. Der var en mening med, at jeg satte dem.

Nu virker det - du skal have mange tak!

Jeg så godt min fejl med dollartegn men tak alligevel, lidt surt hvis referencerne ikke passer!

Jeg er ikke så skarp i matrix formler er det muligt at forklare præcis hvad der sker?

og husk dine point...

Ja det er muligt, men hvis ikke du kender noget videre til matriksformler er det måske ikke lige den mest oplagte formel at begynde med.

Hvis du er interesseret i at lære om matriksformler så prøv Google. Der er masser af artikler om emnet. Enndvidere er det helt nødvendigt, at du er eller gør dig fortrolig med at markere en del af formlen og trykke på F9 for at analysere en formels enkeltbestanddele/brug af Evaluer-formel-værktøjet.

Hvis der herefter stadigvæk er noget af formlen du ikke forstår, så kom igen.

Hvis vi ser på de data du nævnte i dit oprindelige spørgsmål, og du har disse data i A1:B8 og du har opslagsværdien i C1 og i D1 formlen:
=INDEKS($B$1:$B$8;MINDSTE(HVIS($A$1:$A$8=$C$1;RÆKKE($A$1:$A$8));RÆKKE(1:1)))

så kan formlen analyseres som følger

Den del af formlen der lyder:
MINDSTE(HVIS($A$1:$A$8=$C$1;RÆKKE($A$1:$A$8));RÆKKE(liste!1:1))
returnerer rækkenumrene på de celler der i A1:A8 indeholder det samme tal som opslagstallet i C1.
Prøv at aktivere celle D1 og (i formelfeltet) marker med musen præcist følgende del af formlen: $A$1:$A$8=$C$1 og tryk på F9. Du vil se følgende: {SAND; SAND; SAND; SAND; FALSK; FALSK; FALSK; FALSK}. Tryk på Esc for at komme tilbage. Prøv på samme måde at markere RÆKKE($A$1:$A$8), tryk igen på F9 og overbevis dig om, at der står: {1;2;3;4;5;6;7;8}.HVIS-formlen er herefter reduceret til:

HVIS({SAND; SAND; SAND; SAND; FALSK; FALSK; FALSK; FALSK};{1;2;3;4;5;6;7;8})
Dette udtryk returnerer {1;2;3;4;FALSK; FALSK; FALSK; FALSK}, hvilket du kan checke ved at markere præcist:
HVIS($A$1:$A$8=$C$1;RÆKKE($A$1:$A$8)) og trykke på F9. Hermed har vi første argument (en matriks) til MINDSTE-funktionen, der herefter ser sådan ud:

MINDSTE({1;2;3;4;FALSK; FALSK; FALSK; FALSK};{1;2;3;4;5;6;7;8}). Andet argument til MINDSTE-funktionen er RÆKKE(1:1). RÆKKE(1:1) returnerer nummeret på række 1, altså 1. MINDSTE-formlen ser herefter således ud:

MINDSTE({1;2;3;4;FALSK; FALSK; FALSK; FALSK};{1;2;3;4;5;6;7;8};RÆKKE(1:1))
Det vil sige, at her returneres altså den mindste værdi i matriksen {1;2;3;4;FALSK; FALSK; FALSK; FALSK}, det vil sige 1. Når formlen kopieres en celle nedad vil RÆKKE(1:1) have ændret sig (på grund af ingen dollartegn) til RÆKKE(2:2). Det vil sige at MINDSTE-formlen nu ser sådan ud:
MINDSTE({1;2;3;4;FALSK; FALSK; FALSK; FALSK};{1;2;3;4;5;6;7;8};RÆKKE(2:2))
som selvfølgelig vil returnere den anden mindste værdi i matriiksen som er 2.Når formlen i D1 kopieres nedad til og med D8, så vil den del af formlen der lyder:

MINDSTE(HVIS($A$1:$A$8=$C$1;RÆKKE($A$1:$A$8));RÆKKE(2:2))

altså returnere:
D1  1
D2  2
D3  3
D4  4
D5  #NUM!
D6  #NUM!
D7  #NUM!
D8  #NUM!

Det vil sige vi står tilbage med INDEKS-funktioner med følgende:

=INDEKS(($B$1:$B$8;{2})
=INDEKS(($B$1:$B$8;{2})
=INDEKS(($B$1:$B$8;{3})
=INDEKS(($B$1:$B$8;{4})
=INDEKS(($B$1:$B$8;{#NUM!})
=INDEKS(($B$1:$B$8;{#NUM!})
=INDEKS(($B$1:$B$8;{#NUM!})
=INDEKS(($B$1:$B$8;{#NUM!})
som returnerer:
30
31
32
33
#NUM!
#NUM!
#NUM!
#NUM!