INDEX für selektierte Zeile in Tabelle

%3CLINGO-SUB%20id%3D%22lingo-sub-2361912%22%20slang%3D%22de-DE%22%3ESubject%3A%20INDEX%20for%20selected%20row%20in%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2361912%22%20slang%3D%22de-DE%22%3EUnfortunately%2C%20I%20can't%20find%20a%20solution%20yet%2C%20here's%20the%20latest%20idea%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20B2%3DINDEX(tblplan%5B%5B%23Kopfzeilen%5D%3B%5B%2006.05.2020%5D%3A%5B27.05.2020%5D%5D%3B1%3B%20MAX((tblplan%5B%5B06.05.2020%5D%3A%5B27.05.2020%5D%5D%3DCOMPARISON(%22x%22%3BINDEX(tblplan%5B%5BSelect%5D%3A%5BSelect%5D%5D%3B0%3B%20COMPARISON(%22BR%3FS01%22%3Btblplan%5B%5B06.05.2020%5D%3A%5B27.05.2020%5D%5D)))%3B0)))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2349650%22%20slang%3D%22de-DE%22%3EINDEX%20for%20selected%20row%20in%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2349650%22%20slang%3D%22de-DE%22%3E%3CTABLE%20border%3D%220%22%20width%3D%22240%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2280%22%20height%3D%2220%22%3EMax%20(selected)%3C%2FTD%3E%3CTD%20width%3D%2280%22%3E01%3C%2FTD%3E%3CTD%20width%3D%2280%22%3E02%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3EBro%3F%20S%3C%2FTD%3E%3CTD%3E20.05.2020%3C%2FTD%3E%3CTD%3E06.05.2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ETa%3F%20S%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E27.05.2020%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDesired%20formula%20in%20B2%20for%20selected%20row%20B8%20%3DINDEX(%5B%23Kopfzeilen%5D%3B1%3B%20MAX((Tblplan%5B-%5B06.05.2020%5D%3A%5B27.05.2020%5D%5D%3DWVERWEIS(%24A%202%26amp%3BB%241%3B%20Tblplan%5B-06.05.2020%5D%3A%5B27.05.2020%5D%5D%3B1%3B%20FALSE)))*SPALTE(Tblplan%5B-%5B06.05.2020%5D%3A%5B27.05.2020%5D%5D)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22480%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2280%22%20height%3D%2220%22%3Ename%3C%2FTD%3E%3CTD%20width%3D%2280%22%3ESelect%3C%2FTD%3E%3CTD%20width%3D%2280%22%3E06.05.2020%3C%2FTD%3E%3CTD%20width%3D%2280%22%3E13.05.2020%3C%2FTD%3E%3CTD%20width%3D%2280%22%3E20.05.2020%3C%2FTD%3E%3CTD%20width%3D%2280%22%3E27.05.2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ELinda%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EBR2S01%3C%2FTD%3E%3CTD%3EBR7S01%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3EMax%3C%2FTD%3E%3CTD%3EX%3C%2FTD%3E%3CTD%3EBR1S02%3C%2FTD%3E%3CTD%3EBR2S01%3C%2FTD%3E%3CTD%3EBR1S01%3C%2FTD%3E%3CTD%3ETA1S02%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%2C%20maybe%20someone%20could%20help%20me%2C%20I%20would%20like%20to%20improve%20the%20table%20performance%20even%20more.%3C%2FP%3E%3CP%3EThe%20target%20is%20a%20list%20of%20when%20a%20specific%20code%20is%20last%20entered%20for%20the%20selected%20person.%3C%2FP%3E%3CP%3ETo%20do%20this%2C%20I%20would%20like%20to%20enter%20index%20formulas%20in%20the%20upper%20table%20(B2%3AC3).%20The%20values%20are%20shown%20in%20the%20lower%20table%2C%20but%20only%20the%20row%20selected%20with%20%22x%22%20should%20be%20used.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECriteria%3A%20running%20on%20Excel%202013%2C%20without%20VBA%2C%20no%20XVERWEIS%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much!!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%23%23%23%3C%2FP%3E%3CP%3EHere%20is%20a%20new%20idea%2C%20unfortunately%20not%20running%3A%3C%2FP%3E%3CP%3E%3CSPAN%3EB2%3DINDEX(tblplan%5B%5B%23Kopfzeilen%5D%3B%5B%2006.05.2020%5D%3A%5B27.05.2020%5D%5D%3B1%3B%20MAX((tblplan%5B%5B06.05.2020%5D%3A%5B27.05.2020%5D%5D%3DCOMPARISON(%22x%22%3BINDEX(tblplan%5B%5BSelect%5D%3A%5BSelect%5D%5D%3B0%3B%20COMPARISON(%22BR%3FS01%22%3Btblplan%5B%5B06.05.2020%5D%3A%5B27.05.2020%5D%5D)))%3B0)))%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2349650%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2379566%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%20f%C3%BCr%20selektierte%20Zeile%20in%20Tabelle%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2379566%22%20slang%3D%22en-US%22%3E%3CP%3E%3CFONT%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1042960%22%20target%3D%22_blank%22%3E%40learner1234%3C%2FA%3E%3C%2FFONT%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESuggested%20improvement%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yea_So_0-1621809376357.png%22%20style%3D%22width%3A%202160px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F283014iBA3F97E62524F59C%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Yea_So_0-1621809376357.png%22%20alt%3D%22Yea_So_0-1621809376357.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3ESample%20File%20Here%3A%26nbsp%3B%3CA%20title%3D%22INDEXf%C3%BCrselektierteZeileinTabelle%20Sample%20File%22%20href%3D%22https%3A%2F%2F1drv.ms%2Fx%2Fs!AiUZUhiQtF3Fgt5P9UwQeJtZ_BeR2A%3Fe%3DOAGM5k%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2F1drv.ms%2Fx%2Fs!AiUZUhiQtF3Fgt5P9UwQeJtZ_BeR2A%3Fe%3DOAGM5k%3C%2FA%3E%3C%2FFONT%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor
Max (selected)0102
BR?S20.05.202006.05.2020
TA?S 27.05.2020

 

Wunschformel in B2 für selektierte Zeile B8 =INDEX([#Kopfzeilen];1;MAX((Tblplan[@[06.05.2020]:[27.05.2020]]=WVERWEIS($A2&B$1;Tblplan[@[06.05.2020]:[27.05.2020]];1;FALSCH))*SPALTE(Tblplan[@[06.05.2020]:[27.05.2020]])))

 

NameSelect06.05.202013.05.202020.05.202027.05.2020
Linda  BR2S01BR7S01 
MaxxBR1S02BR2S01BR1S01TA1S02

 

 

Hallo, vielleicht könnte mir jemand helfen, ich würde gerne die Tabellenperformance noch verbessern.

Ziel ist eine Auflistung, wann für die selektierte Person zuletzt ein bestimmter Code eingetragen ist.

Hierzu möchte ich in die obere Tabelle (B2:C3) Indexformeln eintragen. In der unteren Tabelle stehen die Werte, allerdings soll nur die mit "x" ausgewählte Zeile herangezogen werden.

 

Kriterien: lauffähig auf Excel 2013, ohne VBA, kein XVERWEIS

 

Vielen herzlichen Dank!!!

 

 

###

Hier eine neue Idee, leider nicht lauffähig:

B2=INDEX(tblplan[[#Kopfzeilen];[06.05.2020]:[27.05.2020]];1;MAX((tblplan[[06.05.2020]:[27.05.2020]]=VERGLEICH("x";INDEX(tblplan[[Select]:[Select]];0;VERGLEICH("BR?S01";tblplan[[06.05.2020]:[27.05.2020]])));0)))

1 Reply

Hi @learner1234 

Suggested improvement?

Yea_So_0-1621809376357.png

 

Sample File Here: https://1drv.ms/x/s!AiUZUhiQtF3Fgt5P9UwQeJtZ_BeR2A?e=OAGM5k