• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index Match!

ExcelGeek25

ExcelGeek25

New Member
Hello: I am working on a crazy dataset which is time-sensitive and involves 27k rows/ 63 columns in 1st sheet and 25k rows/ 54 columns in the 2nd sheet of the same workbook. Now, most of the columns are identical but pulled from different CRM engines so data is a bit different given the updates/ dates, etc.

The 2nd sheet has some columns with data that DO NOT exist in the 1st sheet but the EMAIL column in both the sheets is similar and most of the emails in the 1st sheet (27k emails) should cover the emails in the 2nd sheet (25k emails). I have sorted data in both sheets by email but I've never used an INDEX MATCH and hence need help to find the data/ column in 2nd sheet and with the formula import it into the 1st sheet. I know VLOOKUP but haven't tried it as it has several limitations and I have read that INDEX MATCH is a more powerful alternative.

Current example: I have added a 64th column in 1st sheet to pull the data from 2nd sheet. The emails column in 1st sheet is Col E and the full array is Col A:BK while the email (matching column) is in Col B in the 2nd sheet and the column I'm looking to extract data from is Col AE and the full array in 2nd sheet is Col A:BB.

I am sorry I cannot share this information as an example because it is sensitive.

Please help me with some explanation/ the method to source the data I'm looking for. I will be eternally grateful to you.
 
Your I am sorry I cannot share this information as an example because it is sensitive.
You could/should share some sample data, which shows an idea - what do You really have there?
... as well as ... what do You really would like to have?
 
ExcelGeek25 said:
I am sorry I cannot share this information as an example because it is sensitive.
Click to expand...

HOW TO ATTACH YOUR SAMPLE WORKBOOK: Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands) and some manually calculated results. Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised (e.g. contains fake names and personal details). The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.
 
Time-sensitive and no worksheet will not make it easier for you, I usually divide my data by 2/3 and rename my company name as acme , and find replace all A's with x's, and so on. Replace all common names in emails with others ie replace Gmail with proton, etc
 
Last edited:
You must log in or register to reply here.
Back
Top