• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Extracting information from one column of a database based on matching several other columns from another.|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Extracting information from one column of a database based on matching several other columns from another.|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Extracting information from one col…
sp_PrintTopic sp_TopicIcon
Extracting information from one column of a database based on matching several other columns from another.
Avatar
Donald Koop

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
September 11, 2018
sp_UserOfflineSmall Offline
1
September 12, 2018 - 12:11 pm
sp_Permalink sp_Print
A B C D E F G H I J
Primary Database  Secondary Database From "E"
                   
A B C D abcd M N O P  
E F G H efgh E F G H  
I J K L ijkl U V W X  
M N O P mnop A B C D  
Q R S T qrst Q R S T  
U V W X uvwx I J K L  
                   
                   
Problem: Extracting information from one column of a database based on matching several other columns from another.
Each database could contain hundreds or thousands of entries.  I have included a test mock-up of both in one file as will be done in practice.
All of the information in the secondary database came from the larger primary one and now Col. J needs to be updated from Col. E.
The rows of the two databases are sorted differently, but not the columns.      
The need is to match each row in the secondary database to its parent in the primary one and copy the entry in column E into column J
I have been trying to make "IF, INDEX + MATCH" work but can't find the way to do it.    
sp_AnswersTopicSeeAnswer See Answer
Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
2
September 12, 2018 - 12:26 pm
sp_Permalink sp_Print

It would be best if you can upload your file.

sp_AnswersTopicAnswer
Answers Post
Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
3
September 12, 2018 - 1:17 pm
sp_Permalink sp_Print

Hi Donald

I would create a helper column in the Primary database and then do an INDEX/MATCH from the Secondary database.

Please refer example attached.

Hope this helps.

Sunny

Avatar
Donald Koop

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
September 11, 2018
sp_UserOfflineSmall Offline
4
September 14, 2018 - 4:20 am
sp_Permalink sp_Print

Hi Sunny,

#1 - The actual data is privileged and I should not make it public.

#2 - You may be on to something here.  I could concatenate columns A-D and F-I to produce a single column on each side for comparison.  The only variable left to explore would be which rows match.  I'll pursue this strategy to see if I can figure it out.

Thanks for your comments!

Donald

Avatar
Donald Koop

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
September 11, 2018
sp_UserOfflineSmall Offline
5
September 16, 2018 - 4:41 am
sp_Permalink sp_Print sp_EditHistory

I'm getting closer!

Based on a suggestion, I concatenated columns A-D and G-J to produce a single column on each side for comparison, labeled as E and K. 

Row A B C D E F G H I J K L M N
  Primary Database    Secondary Database       From "F"
                             
20 A B C D ABCD abcd M N O P MNOP 4 23 mnop
21 E F G H EFGH efgh E F G H EFGH 2 21  
22 I J K L IJKL ijkl U V W X UVWX 6 25  
23 M N O P MNOP mnop A B C D ABCD 1 20  
24 Q R S T QRST qrst Q R S T QRST 5 24  
25 U V W X UVWX uvwx I J K L IJKL 3 22  

In column L, row 20 I entered =(MATCH(K20,$E$20:$E$25,0)) which correctly identified the only match in column E as being the 4th entry, shown in column L.  From this, I calculated the actual row number as being 23 (column M).  (All of the other rows were correctly matched, too.)

Here's where I ran into trouble.  Manually entering =E23 in column N, row 20 placed the correct value (mnop) in that cell.  Any other way I tried to do the same thing computationally rather than manually either yielded an error code or =E23 itself that would not retrieve mnop.  This included various attempts to use CONCATENATE and CELL.  I suspect there is a format problem (text vs. something else) that I have not uncovered.

How do I computationally perform the manual equivalent of placing =E23 in N20?

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
6
September 16, 2018 - 5:18 pm
sp_Permalink sp_Print sp_EditHistory

Hi Donald

In cell N20 your formula should be =INDEX($F$20:$F$25,MATCH(K20,$E$20:$E$25,0))

You can refer here for more details on INDEX/MATCH

https://www.myonlinetraininghu.....-functions

It is always best to attach a sample file (with test data of course) as it will be easier to see actually what is wrong, especially when dealing with cell formats/extra space etc.

Sunny

Avatar
Donald Koop

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
September 11, 2018
sp_UserOfflineSmall Offline
7
September 17, 2018 - 9:32 am
sp_Permalink sp_Print

You were so close, Sunny.  It's =INDEX($E$20:$F$25,MATCH(K20,$E$20:$E$25,0),2).  Thanks for the vital lead!!  Donald

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Chris Warren
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Sopi Yuniarti
LAFONSO HERNANDEZ
Hayden Hao
Angela chen
Sean Moore
John Chisholm
Forum Stats:
Groups: 3
Forums: 24
Topics: 6214
Posts: 27246

 

Member Stats:
Guest Posters: 49
Members: 31897
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.