SOLVED

How to search for data independently from sorting

%3CLINGO-SUB%20id%3D%22lingo-sub-1754550%22%20slang%3D%22en-US%22%3EHow%20to%20search%20for%20data%20independently%20from%20sorting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1754550%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Community%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Edo%20you%20have%20any%20any%20ideas%20how%20I%20can%20get%20the%20same%20data%20from%20sheet%201%20to%20sheet%202%20even%20when%20I%20apply%20the%20sort%20function%20in%20sheet%202%20%3F%3C%2FP%3E%3CP%3EE.g.%20I%20need%20the%20value%20of%20Item%20X%20from%20sheet%202%20in%20sheet%201.%20But%20I%20also%20want%20to%20be%20able%20to%20sort%20in%20sheet%202%20and%20the%20value%20of%20Item%20X%20remains%20the%20same%20in%20sheet%201.%20The%20Items%20all%20have%20their%20unique%20%22serialnumber%22%20I%20am%20searching%20for.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20an%20example%2C%20but%20since%20I%60m%20from%20Germany%20I%20have%20to%20use%20%22SVERWEIS%22%20instead%20of%20%22VLOOKUP%22.%3C%2FP%3E%3CP%3EI%20hope%20my%20problem%20definition%20is%20understandable%20and%20that%20you%20can%20help%20me%20out.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20a%20lot%20and%20best%20regards%3C%2FP%3E%3CP%3EHannes%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1754550%22%20slang%3D%22en-US%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-1754668%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20search%20for%20data%20independently%20from%20sorting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1754668%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F823671%22%20target%3D%22_blank%22%3E%40Hannes_8%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAdd%20FALSE%20as%204th%20argument%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DVLOOKUP(E4%2CTabelle2!I%3AK%2C3%2CFALSE)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSVERWEIS(E4%3BTabelle2!I%3AK%3B3%3BFALSCH)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello Community,

 

do you have any any ideas how I can get the same data from sheet 1 to sheet 2 even when I apply the sort function in sheet 2 ?

E.g. I need the value of Item X from sheet 2 in sheet 1. But I also want to be able to sort in sheet 2 and the value of Item X remains the same in sheet 1. The Items all have their unique "serialnumber" I am searching for.

 

I have attached an example, but since I`m from Germany I have to use "SVERWEIS" instead of "VLOOKUP".

I hope my problem definition is understandable and that you can help me out.

 

Thanks a lot and best regards

Hannes

2 Replies
Highlighted
Best Response confirmed by Hannes_8 (New Contributor)
Solution

@Hannes_8 

Add FALSE as 4th argument:

 

=VLOOKUP(E4,Tabelle2!I:K,3,FALSE)

 

=SVERWEIS(E4;Tabelle2!I:K;3;FALSCH)

Highlighted

@Hans Vogelaar 

 

Thank You very much ! It works perfectly.