SOLVED
Home

How search one value in excel and get all results?

%3CLINGO-SUB%20id%3D%22lingo-sub-440694%22%20slang%3D%22en-US%22%3EHow%20search%20one%20value%20in%20excel%20and%20get%20all%20results%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-440694%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3CBR%20%2F%3EI%20am%20working%20on%20my%20thesis.%20I%20need%20to%20search%20one%20value%20in%20excel%20and%20get%20all%20results.%20I%20used%20vlookup%20formula%20but%20it%20only%20gives%20one%20result.%20Can%20you%20please%20instruct%20me%20how%20to%20change%20vlookup%20formula%3F%3CBR%20%2F%3EFor%20example%2C%20in%20the%20table%20below%20I%20want%20to%20search%20NOA1%20from%20between%2010000%20number%20of%20data%20and%20get%20the%20exact%20names%20in%20front%20of%20it%20from%20thousand%20of%20results.%20How%20should%20I%20write%20the%20formula%3F%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3ENOA1%3C%2FTD%3E%3CTD%3Ehsa-miR-640%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ENOA1%3C%2FTD%3E%3CTD%3Ehsa-miR-378a-5p%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ENOA1%3C%2FTD%3E%3CTD%3Ehsa-miR-4436b-5p%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ENOA1%3C%2FTD%3E%3CTD%3Ehsa-miR-6821-3p%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ENOA1%3C%2FTD%3E%3CTD%3Ehsa-miR-4312%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ENOA1%3C%2FTD%3E%3CTD%3Ehsa-miR-3653-5p%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ENOA1%3C%2FTD%3E%3CTD%3Ehsa-miR-6790-3p%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ENOA1%3C%2FTD%3E%3CTD%3Ehsa-miR-3934-5p%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ENOA1%3C%2FTD%3E%3CTD%3Ehsa-miR-764%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ENOA1%3C%2FTD%3E%3CTD%3Ehsa-miR-125a-3p%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-440694%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EUser%20Adoption%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-442201%22%20slang%3D%22en-US%22%3ERe%3A%20How%20search%20one%20value%20in%20excel%20and%20get%20all%20results%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-442201%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F319661%22%20target%3D%22_blank%22%3E%40Yousef72_Yud%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhy%20don't%20you%20use%20'Filter'%20option%20for%20searching%20a%20value%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMay%20be%20'find%20all'%20function%20help%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-442690%22%20slang%3D%22en-US%22%3ERe%3A%20How%20search%20one%20value%20in%20excel%20and%20get%20all%20results%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-442690%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20your%20answer.%20Becuase%20i%20have%20so%20many%20values%20to%20search%20and%20excel%20filter%20don't%20show%20the%20results%20more%20than%2010000%20%2C%20filtering%20can't%20help.%20I%20will%20try%20find%20all%20function%20.%20thanks%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F53299%22%20target%3D%22_blank%22%3E%40Logaraj%20Sekar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-442749%22%20slang%3D%22en-US%22%3ERe%3A%20How%20search%20one%20value%20in%20excel%20and%20get%20all%20results%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-442749%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F53299%22%20target%3D%22_blank%22%3E%40Logaraj%20Sekar%3C%2FA%3E%26nbsp%3B%26nbsp%3B%3CBR%20%2F%3EI%20tried%20the%20filter%20and%20find%20function.%20The%20problem%20is%20i%20want%20to%20search%20almost%20500%20number%20of%20values%20between%20thousands%20of%20cells%20and%20in%20result%20take%20bake%20the%20exact%20results%20which%20is%20written%20in%20front%20of%20it.%20I%20found%20vlookup%20as%20the%20best%20function.%20But%20i%20think%20it%20needs%20improvements%2C%20can%20you%20please%20help%20me%20to%20write%20the%20best%20formula%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-443359%22%20slang%3D%22en-US%22%3ERe%3A%20How%20search%20one%20value%20in%20excel%20and%20get%20all%20results%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-443359%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F319661%22%20target%3D%22_blank%22%3E%40Yousef72_Yud%3C%2FA%3E%26nbsp%3B%2C%20by%20the%20way%2C%2010000%20is%20the%20limit%20only%20for%20drop%20down%20list%2C%20filter%20itself%20has%20no%20limits%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-443610%22%20slang%3D%22en-US%22%3ERe%3A%20How%20search%20one%20value%20in%20excel%20and%20get%20all%20results%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-443610%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3EThanks%3CBR%20%2F%3EI%20there%20a%20way%20to%20filter%20almost%20500%20values%20at%20the%20same%20time%3F%3CBR%20%2F%3Ecan%20you%20please%20how%20can%20i%20access%20to%20that%20filtering%20system%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-444072%22%20slang%3D%22en-US%22%3ERe%3A%20How%20search%20one%20value%20in%20excel%20and%20get%20all%20results%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-444072%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F319661%22%20target%3D%22_blank%22%3E%40Yousef72_Yud%3C%2FA%3E%26nbsp%3B%2C%20there%20are%20these%20500%20values%20kept%3F%20If%20you%20have%20your%20main%20list%20with%20dozen%20thousand%20of%20records%20and%20another%20one%20with%20500%20values%2C%20based%20on%20which%20you'd%20like%20to%20extract%20data%20from%20the%20first%20list%2C%20with%20Power%20Query%20you%20may%20query%20both%20lists%2C%20merge%20them%20for%20values%20only%20from%20the%20second%20list%20and%20land%20result%20back%20to%20Excel%20sheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-448620%22%20slang%3D%22en-US%22%3ERe%3A%20How%20search%20one%20value%20in%20excel%20and%20get%20all%20results%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-448620%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3EThanks%20a%20lot%3CBR%20%2F%3EI%20could%20solve%20the%20problem%20by%20combining%20multiple%20formulas%20together%20including%20index.%20aggregation%2C%20etc.%20It%20is%20completely%20working%20for%20me.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-449482%22%20slang%3D%22en-US%22%3ERe%3A%20How%20search%20one%20value%20in%20excel%20and%20get%20all%20results%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-449482%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F319661%22%20target%3D%22_blank%22%3E%40Yousef72_Yud%3C%2FA%3E%26nbsp%3B%2C%20yes%2C%20also%20a%20variant%2C%20just%20with%20Power%20Query%20that's%20much%20easier.%20Anyway%2C%20great%20to%20know%20you%20solved%20that.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Yousef72_Yud
Occasional Contributor

Hello
I am working on my thesis. I need to search one value in excel and get all results. I used vlookup formula but it only gives one result. Can you please instruct me how to change vlookup formula?
For example, in the table below I want to search NOA1 from between 10000 number of data and get the exact names in front of it from thousand of results. How should I write the formula? 

NOA1hsa-miR-640
NOA1hsa-miR-378a-5p
NOA1hsa-miR-4436b-5p
NOA1hsa-miR-6821-3p
NOA1hsa-miR-4312
NOA1hsa-miR-3653-5p
NOA1hsa-miR-6790-3p
NOA1hsa-miR-3934-5p
NOA1hsa-miR-764
NOA1hsa-miR-125a-3p
8 Replies

@Yousef72_Yud 

 

Why don't you use 'Filter' option for searching a value?

 

May be 'find all' function help you.

Thanks for your answer. Becuase i have so many values to search and excel filter don't show the results more than 10000 , filtering can't help. I will try find all function . thanks

@Logaraj Sekar 

@Logaraj Sekar  
I tried the filter and find function. The problem is i want to search almost 500 number of values between thousands of cells and in result take bake the exact results which is written in front of it. I found vlookup as the best function. But i think it needs improvements, can you please help me to write the best formula? 

@Yousef72_Yud , by the way, 10000 is the limit only for drop down list, filter itself has no limits

@Sergei Baklan 
Thanks
I there a way to filter almost 500 values at the same time?
can you please how can i access to that filtering system?

 

Solution

@Yousef72_Yud , there are these 500 values kept? If you have your main list with dozen thousand of records and another one with 500 values, based on which you'd like to extract data from the first list, with Power Query you may query both lists, merge them for values only from the second list and land result back to Excel sheet.

@Sergei Baklan 
Thanks a lot
I could solve the problem by combining multiple formulas together including index. aggregation, etc. It is completely working for me. 

@Yousef72_Yud , yes, also a variant, just with Power Query that's much easier. Anyway, great to know you solved that.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies