SOLVED

how to match the list data from different excel sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-1639091%22%20slang%3D%22en-US%22%3Ehow%20to%20match%20the%20list%20data%20from%20different%20excel%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1639091%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20am%20new%20to%20excel%20and%20I%20looked%20everywhere%20for%20the%20following%20Q%26amp%3BA%20but%20I%20couldn't%20find%20any%2C%20unless%20I%20missed%20smh.%3C%2FP%3E%3CP%3EI%20have%20list%20data%20and%20I%20want%20to%20place%20a%20result%20for%20certain%20match%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22Screenshot%202020-09-06%20at%204.34.00%20PM.png%22%20style%3D%22width%3A%20391px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F216811i14B4141BEB6FA2B7%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Screenshot%202020-09-06%20at%204.34.00%20PM.png%22%20alt%3D%22Fig.1%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EFig.1%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3BFig.1%20data%20is%20extracted%20from%20Fig.2%20and%20removed%20duplicates.%20I%20added%20column%2C%20C%20a%20result%20that%20should%20show.%20So%20that%20I%20can%20use%20this%20data%20in%20fig.1.%20This%20will%20help%20me%20if%20the%20data%20is%20huge.%20%26nbsp%3BCan%20anyone%20help%20me%20out%20pls.%20Any%20formula%20or%20functions%20or%20any%20sort%20to%20help%20me%20will%20be%20great.%20Thanks%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22Screenshot%202020-09-06%20at%204.34.50%20PM.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F216812iFCB2F2749CCC555C%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Screenshot%202020-09-06%20at%204.34.50%20PM.png%22%20alt%3D%22Fig.2%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EFig.2%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1639091%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1639145%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20match%20the%20list%20data%20from%20different%20excel%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1639145%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F783518%22%20target%3D%22_blank%22%3E%40Krishck%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhy%20not%20upload%20a%20sample%20Excel%20file%20instead%20of%20image%20as%20images%20are%20difficult%20to%20work%20with%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1639150%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20match%20the%20list%20data%20from%20different%20excel%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1639150%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3BHi%20thank%20you%20for%20response.%20I%20have%20uploaded%20the%20file.%20It%20would%20be%20great%20if%20there's%20anyway%20to%20work%20using%20formulae%20or%20any%20insight%20for%20future%2C%20when%20I%20am%20working%20with%20thousands%20of%20rows.%20As%20that%20will%20help%20me%20a%20lot%20for%20future%20reference.%20%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1639151%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20match%20the%20list%20data%20from%20different%20excel%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1639151%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F783518%22%20target%3D%22_blank%22%3E%40Krishck%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20want%20to%20get%20the%20result%20value%20from%20the%20table%20sheet%20into%20the%20Sales%20Orders%20sheet%20then%20if%20you%20have%20XLOOKUP%20you%20can%20type%20this%20into%20cell%20D2%20of%20the%20Sales%20Order%20Sheet%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DXLOOKUP(B2%26amp%3BC2%2C%20table!%24A%242%3A%24A%2412%20%26amp%3B%20table!%24B%242%3A%24B%2412%2C%20table!%24C%242%3A%24C%2412)%3CBR%20%2F%3E%3CBR%20%2F%3EMy%20other%20tip%20would%20be%20turn%20the%20table%20of%20lookup%20values%20on%20the%20table%20sheet%20into%20a%20proper%20Table%20by%20clicking%20in%20the%20data%20and%20pressing%20Ctrl%20t%20before%20you%20then%20add%20the%20formula%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi, I am new to excel and I looked everywhere for the following Q&A but I couldn't find any, unless I missed smh.

I have list data and I want to place a result for certain matchFig.1Fig.1

 Fig.1 data is extracted from Fig.2 and removed duplicates. I added column, C a result that should show. So that I can use this data in fig.1. This will help me if the data is huge.  Can anyone help me out pls. Any formula or functions or any sort to help me will be great. Thanks 

Fig.2Fig.2

 

7 Replies

@Krishck 

Why not upload a sample Excel file instead of image as images are difficult to work with?

@Subodh_Tiwari_sktneer Hi thank you for response. I have uploaded the file. It would be great if there's anyway to work using formulae or any insight for future, when I am working with thousands of rows. As that will help me a lot for future reference.  

Best Response confirmed by Krishck (Occasional Contributor)
Solution

Hi @Krishck

 

If you want to get the result value from the table sheet into the Sales Orders sheet then if you have XLOOKUP you can type this into cell D2 of the Sales Order Sheet

 

=XLOOKUP(B2&C2, table!$A$2:$A$12 & table!$B$2:$B$12, table!$C$2:$C$12)

My other tip would be turn the table of lookup values on the table sheet into a proper Table by clicking in the data and pressing Ctrl t before you then add the formula

Suggested solution attached

 

XLOOKUP is not backwards compatible with older versions of Excel

@Krishck 

 

If you don't have XLOOKUP then here's the solution using INDEX MATCH

 

 

Thank you this has solved my problem. If I have two worksheets in same workbook, and I am comparing sales orders from previous quarter and see how many new orders are available. Can I still use XLOOKUP or VLOOKUP for that data? Due to work rules, I am unable to post the actual worksheets.

Maybe, or maybe a COUNTIFS. I'd suggest posting a new thread. I'm signing off for today