Leta värde utifrån flera referenser i Excel

[Klimpen]

Hej!

 

Hur gör man för att leta upp ett värde utifrån två referenser på samma rad i Excel?

 

Med LETARAD kan man ju bara söka på ett värde och då bara det som är längst till vänster i matrisen.

 

Exempel: Man har en lista med förnamn, efternamn och ålder och man i ett annat blad vill genom att skriva förnamn och efternamn i olika celler i en tredje cell automatiskt få åldern på denna person.

 

PS. jag vill inte ändra något i bladet där listan finns. DS.

 

Exempel på lista:

 

FörnamnEfternamnÅlderKalleSvensson21KalleNilsson22KalleAndersson23Nisse Andersson23Nisse Svensson24Nisse Nilsson22

Tacksam för hjälp / Klimpen

[Monshi]

Enkla sättet

du skriver ihop de två referenserna du vill söka på en ny kolumn i tabellen och får därmed bara en kolumn att söka i.

 

Svårare sättet, då blir det matrisformler eller liknande. Vilken version av Excel har du och finns det bara en unik träff eller kan det bara flera som är lika?

[Klimpen]

Enkla sättet

du skriver ihop de två referenserna du vill söka på en ny kolumn i tabellen och får därmed bara en kolumn att söka i.

 

Svårare sättet, då blir det matrisformler eller liknande. Vilken version av Excel har du och finns det bara en unik träff eller kan det bara flera som är lika?

 

 

Skulle behöva köra med det svåra sättet då vi inte vill ändra något i filen med databasen. Vi kommer jobba i Excel 2007 men spara det i 2000-2003-format.

 

Det finns bara en unik träff, dvs. att samma kombination av förnamn och efternamn endast finns en gång.

 

Jag bifogar en excel-fil som exempel. Kanske är lättast att skriva en fungerande formel däri och förklara för att vi lättast ska förstå.

 

Tack på förhand!

 

/Klimpen

Test.xls

[Monshi]

En matrisformel får det då bli

{ =MAX(($C$9:$C$14=D2)*($D$9:$D$14=D3)*$E$9:$E$14) }

måsvingarna ska du inte kopiera, de infogar Excel när du matar in det som en matrisformel. Det vill säga, avsluta inmatningen med Ctrl+Shift+Enter.

 

Dock ett litet varningens ord, matrisformler är beräkningsintensiva. Den lilla formeln ovan är alltså 6*6*6 flyttalsoperationer (minst), dvs 216 stycken. Rent matematiskt alltså minst n^3 operationer där n är antalet rader.

En formel inget problem. Två inte heller. Men många formler och stora tabeller kan ge problem.

[Klimpen]

En matrisformel får det då bli

{ =MAX(($C$9:$C$14=D2)*($D$9:$D$14=D3)*$E$9:$E$14) }

måsvingarna ska du inte kopiera, de infogar Excel när du matar in det som en matrisformel. Det vill säga, avsluta inmatningen med Ctrl+Shift+Enter.

 

Dock ett litet varningens ord, matrisformler är beräkningsintensiva. Den lilla formeln ovan är alltså 6*6*6 flyttalsoperationer (minst), dvs 216 stycken. Rent matematiskt alltså minst n^3 operationer där n är antalet rader.

En formel inget problem. Två inte heller. Men många formler och stora tabeller kan ge problem.

 

 

Hej,

 

Tack för hjälpen. Den fungerade dock inte riktigt. Den funkade så länge man hade Kalle som förnamn men när man skrev Nisse blev resultatet 0.

 

Vet du vad som kan ha gått fel?

 

I det fall som vi kommer att använda detta handlar det om 2 beräkningar och en databas på drygt 3000 rader. Tror du detta blir för mycket?

 

Mvh / Klimpen

[Monshi]

Att Nisse inte vill vara med är för att Nisse har ett extra mellanslag efter sitt namn. det står alltså

"Nisse " och inte "Nisse" i cellerna.

 

Ett annat alternativ är att använda databasfunktionerna, kanske bättre. Det är inte var dag jag använder dem

 

Vrid på dina villkor:

där det nu står en ålder, skriv in

=DHÄMTA(C8:E14;H2;F2:G3)

 

Den ger praktiskt nog ett felvärde om det finns flera lika poster, är nog att föredra när jag tänker efter. Är med största sannolikhet snabbare än max-formeln jag gav tidigare.

Men - Nisse kommer att ge fel än, städa dina data.

Och som alltid - om du inte vill ha själva sökområdet synligt är det bara att dölja det och länka in värdena dit.

 

Ed: Noterar Nisse ger inte fel med denna funktion. Faktum är att Nisse Svensson hittas bara du skriva N och S i villkorsfälten...

Så kanske man även ska skriva in två DHÄMTA som hämtar det faktiska namnet? ja, det blir din hemuppgift att skapa DHÄMTA som gör det

 

Note to myself

databasfunktionerna är praktiska! Använd dem!

ok

[Axi]

Du kan även komplettera med funktionen RENSA på dina celler. Tar bort blanksteg i början och slutet men inte i mitten.

[Klimpen]

Du kan även komplettera med funktionen RENSA på dina celler. Tar bort blanksteg i början och slutet men inte i mitten.

 

 

Just det, den kan ju vara nåt

 

Nu fungerar det iaf så stort tack för hjälpen.

 

Nu har vi dock stött på ett annat problem. Har du något bra tips på hur man kan köra motsvarande LETARAD fast bakåt, dvs åt vänster från "leta upp-värdet"?

 

Problemet nu är att vi i ett annat fall ska leta upp ett värde på samma sätt som förut fast med skillnaden att det finns flera rader med det värde som vi använder som referenser. Alla rader innehåller dock, i de kollumner som vi ska hämta information ifrån, samma information.

 

Med andra ord behöver vi köra en LETARAD eller liknande åt vänster på "första bästa" rad som motsvarar vår referens.

 

Mvh / Klimpen

[Monshi]

Du menar LETAKOLUMN()?

 

Eller för den delen mer generella PASSA()?

 

Hjärnkontoret lite förvirrat idag - ge ett klart exempel på vad du har och vad du vill få ut.

[Axi]

Jo, det kan gå om du fuskar lite. Dock inte med Letarad.

 

=INDIREKT("A"&PASSA(E2;B:B;0))

 

"A" är den kolumnen du vill hämta värdet från. (Ändra vid behov)

E2 är ditt sökkriterium. (Ändra vid behov)

B:B är din sökrange. (Ändra vid behov)

 

Det du gör här att att leta upp radnummer med PASSA() och därefter använder du INDIREKT() för att justera fokus till en annan kolumn på samma rad och visa värdet i den cellen. Skulle eventuellt kunna gå med FÖRSKJUTNING() istället för INDIREKT().

[Klimpen]

Du menar LETAKOLUMN()?

 

Eller för den delen mer generella PASSA()?

 

Hjärnkontoret lite förvirrat idag - ge ett klart exempel på vad du har och vad du vill få ut.

 

 

Hej,

 

Vi körde med FÖRSKJUTNING och PASSA som Axi tipsade om så nu är vi (förhoppningsvis) på det klara med de grejer vi behöver.

 

Stort tack för hjälpen.

 

Mvh / Klimpen

[Klimpen]

Jo, det kan gå om du fuskar lite. Dock inte med Letarad.

 

=INDIREKT("A"&PASSA(E2;B:B;0))

 

"A" är den kolumnen du vill hämta värdet från. (Ändra vid behov)

E2 är ditt sökkriterium. (Ändra vid behov)

B:B är din sökrange. (Ändra vid behov)

 

Det du gör här att att leta upp radnummer med PASSA() och därefter använder du INDIREKT() för att justera fokus till en annan kolumn på samma rad och visa värdet i den cellen. Skulle eventuellt kunna gå med FÖRSKJUTNING() istället för INDIREKT().

 

 

Hej,

 

Vi körde med FÖRSKJUTNING och det funkade klockrent.

 

Tack för hjälpen!

 

Mvh

Klimpen

[Niko197609]

En matrisformel får det då bli

{ =MAX(($C$9:$C$14=D2)*($D$9:$D$14=D3)*$E$9:$E$14) }

måsvingarna ska du inte kopiera, de infogar Excel när du matar in det som en matrisformel. Det vill säga, avsluta inmatningen med Ctrl+Shift+Enter.

 

Dock ett litet varningens ord, matrisformler är beräkningsintensiva. Den lilla formeln ovan är alltså 6*6*6 flyttalsoperationer (minst), dvs 216 stycken. Rent matematiskt alltså minst n^3 operationer där n är antalet rader.

En formel inget problem. Två inte heller. Men många formler och stora tabeller kan ge problem.

 

 

Hej,

 

Om värdet ligger med ett minus tecken så returneras vädert 0. Varför? Kan man fixa funktionen för att det tillexempel visar -21?

 

Mvh

 

Nik

[Monshi]

Vore bra om du skapade en egen tråd med ditt problem. Okej om du länkar tillbaka till denna, men skapa en egen.

 

Som svar på din fråga är det pga av att alla rader där villkoren inte är sanna kommer att returnera nollvärde vilket är större än -21

 

Men som sagt, skapa en ny tråd.