Microsoft Office Forums How to match one list with another and return certain values from corresponding cells in excel

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-08-2019, 09:20 AM
Oluagbe1 Oluagbe1 is offline How to match one list with another and return certain values from corresponding cells in excel Windows 10 How to match one list with another and return certain values from corresponding cells in excel Office 2019
Novice
How to match one list with another and return certain values from corresponding cells in excel
 
Join Date: Feb 2019
Posts: 5
Oluagbe1 is on a distinguished road
Default How to match one list with another and return certain values from corresponding cells in excel

Hello everyone.
Please i need help with my spreadsheet that has multiple information on it.

What i want to do is to pick each years TOTAL SCORE from the yearly sheets and populate the same total scores into the appropriate columns in the cumulative sheet.

And i am supposed to get these scores by matching 2 unique values which are the NAMES, AND ORACLE NO. And if these informations are present, then it will return the total score of that individual into its appropriate year.

The name and oracle number is unique to individuals except where we probably have typographical errors else it's a unique value to all individuals.



Please anyone help me with this task as i have tried severally but all to no avail.

Thank you all.
Attached Files
File Type: xlsx done 2.xlsx (64.9 KB, 4 views)
Reply With Quote
  #2  
Old 02-08-2019, 12:03 PM
NBVC's Avatar
NBVC NBVC is offline How to match one list with another and return certain values from corresponding cells in excel Windows 10 How to match one list with another and return certain values from corresponding cells in excel Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 202
NBVC will become famous soon enough
Default

Try:


=SUMIFS(INDIRECT("'year "&RIGHT(F$1,2)&"'!$G:$G"),INDIRECT("'year "&RIGHT(F$1,2)&"'!$B:$B"),$C2,INDIRECT("'year "&RIGHT(F$1,2)&"'!$D:$D"),$D2)

copied across and down
Reply With Quote
  #3  
Old 02-08-2019, 01:30 PM
Oluagbe1 Oluagbe1 is offline How to match one list with another and return certain values from corresponding cells in excel Windows 10 How to match one list with another and return certain values from corresponding cells in excel Office 2019
Novice
How to match one list with another and return certain values from corresponding cells in excel
 
Join Date: Feb 2019
Posts: 5
Oluagbe1 is on a distinguished road
Default

Quote:
Originally Posted by NBVC View Post
Try:


=SUMIFS(INDIRECT("'year "&RIGHT(F$1,2)&"'!$G:$G"),INDIRECT("'year "&RIGHT(F$1,2)&"'!$B:$B"),$C2,INDIRECT("'year "&RIGHT(F$1,2)&"'!$D:$D"),$D2)

copied across and down

Thank you very much for your response.

The formula you sent worked perfectly well in the test attachment I sent at the beginning of the thread however when I used it on the actual document, it did not work and I can't explain why.

Please I need more assistance and I have also attached a copy of the actual document here
Attached Files
File Type: xls DATA 1.xls (483.0 KB, 4 views)
Reply With Quote
  #4  
Old 02-11-2019, 05:47 AM
NBVC's Avatar
NBVC NBVC is offline How to match one list with another and return certain values from corresponding cells in excel Windows 10 How to match one list with another and return certain values from corresponding cells in excel Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 202
NBVC will become famous soon enough
Default

The matches must be exact matches. I noted many times you have Mr or Mrs in brackets following the names in the 2015, 2016 and 2017 but in the cumulative sheet the Mr and Mrs is in front of the names...
Reply With Quote
  #5  
Old 02-11-2019, 06:01 AM
Marcia's Avatar
Marcia Marcia is offline How to match one list with another and return certain values from corresponding cells in excel Windows 7 32bit How to match one list with another and return certain values from corresponding cells in excel Office 2007
Competent Performer
 
Join Date: May 2018
Location: Philippines
Posts: 201
Marcia is on a distinguished road
Default

Review the names in the cummulative sheet, they do not exactly match the names in the years sheet. You were not consistent on the placements of the "Mrs" and "Mr". Also, if you want to be alerted of duplicated Oracle Numbers, a simple conditional formatting will do that for you.
Reply With Quote
  #6  
Old 02-11-2019, 06:07 AM
Oluagbe1 Oluagbe1 is offline How to match one list with another and return certain values from corresponding cells in excel Windows 10 How to match one list with another and return certain values from corresponding cells in excel Office 2019
Novice
How to match one list with another and return certain values from corresponding cells in excel
 
Join Date: Feb 2019
Posts: 5
Oluagbe1 is on a distinguished road
Default

You are very correct, the names are not consistent because I got them from different sources and I don't have control over it.

Can you please help me use oy the oracle numbers to update the cumulative sheet then?

So if there are duplicate oracles, when can sort that update manually atlease it will reduce the stress of going through all the files.
Reply With Quote
  #7  
Old 02-11-2019, 06:16 AM
Marcia's Avatar
Marcia Marcia is offline How to match one list with another and return certain values from corresponding cells in excel Windows 7 32bit How to match one list with another and return certain values from corresponding cells in excel Office 2007
Competent Performer
 
Join Date: May 2018
Location: Philippines
Posts: 201
Marcia is on a distinguished road
Default

Are you okay if I replace the registration number with the name?
Reply With Quote
  #8  
Old 02-11-2019, 06:37 AM
Marcia's Avatar
Marcia Marcia is offline How to match one list with another and return certain values from corresponding cells in excel Windows 7 32bit How to match one list with another and return certain values from corresponding cells in excel Office 2007
Competent Performer
 
Join Date: May 2018
Location: Philippines
Posts: 201
Marcia is on a distinguished road
Default

I'm sorry NBVC, I missed reading your second post. At any rate, Oluagbe, I removed the name in the criteria but it would be best if you add the registration number in the criteria. You change "$B:$B" to "$C:$C" (The "C" is the column number of the registration numbers in the year sheets) and the C2 to E2. C2 is the cell address of the name in the cummulative sheet while E2 is the registration number.


I've placed the conditional formatting in columns D and E. Try copying a number and pasting below it, the duplicate number should be colored.
Attached Files
File Type: xls Copy of DATA 1.xls (499.5 KB, 2 views)
Reply With Quote
  #9  
Old 02-11-2019, 07:02 AM
Oluagbe1 Oluagbe1 is offline How to match one list with another and return certain values from corresponding cells in excel Windows 10 How to match one list with another and return certain values from corresponding cells in excel Office 2019
Novice
How to match one list with another and return certain values from corresponding cells in excel
 
Join Date: Feb 2019
Posts: 5
Oluagbe1 is on a distinguished road
Default

Quote:
Originally Posted by Marcia View Post
Are you okay if I replace the registration number with the name?
Yes it's ok with me, in as much as it will make the job more easier.
Reply With Quote
  #10  
Old 02-11-2019, 07:03 AM
Oluagbe1 Oluagbe1 is offline How to match one list with another and return certain values from corresponding cells in excel Windows 10 How to match one list with another and return certain values from corresponding cells in excel Office 2019
Novice
How to match one list with another and return certain values from corresponding cells in excel
 
Join Date: Feb 2019
Posts: 5
Oluagbe1 is on a distinguished road
Default

Quote:
Originally Posted by Marcia View Post
I'm sorry NBVC, I missed reading your second post. At any rate, Oluagbe, I removed the name in the criteria but it would be best if you add the registration number in the criteria. You change "$B:$B" to "$C:$C" (The "C" is the column number of the registration numbers in the year sheets) and the C2 to E2. C2 is the cell address of the name in the cummulative sheet while E2 is the registration number.


I've placed the conditional formatting in columns D and E. Try copying a number and pasting below it, the duplicate number should be colored.

Thank you very much.

I was thinking what if we use just one criteria which is more stable that is the oracle numbers?

This should even populate more cells with the actual scores from the yearly sheets into the cumulative sheet.
Reply With Quote
  #11  
Old 02-11-2019, 07:00 PM
Marcia's Avatar
Marcia Marcia is offline How to match one list with another and return certain values from corresponding cells in excel Windows 7 32bit How to match one list with another and return certain values from corresponding cells in excel Office 2007
Competent Performer
 
Join Date: May 2018
Location: Philippines
Posts: 201
Marcia is on a distinguished road
Default

Quote:
I was thinking what if we use just one criteria which is more stable that is the oracle numbers?
I edited NBVC's formula, removing the name in the criteria so It's just the Oracle numbers. You will notice the cells with colored background caused by the conditional formatting. You type the correct oracle and registration numbers. I intentionally typed numbers in those cells to check if the CFs are working.
I deleted the columns with no data and several rows at the bottom in order to meet the limit of not more than 500 kb.
Attached Files
File Type: xls DATA1.ORACLE.xls (498.0 KB, 3 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to match one list with another and return certain values from corresponding cells in excel Index Match Formula to return value from a list with criteria Marcia Excel 4 10-26-2018 08:59 AM
How to match one list with another and return certain values from corresponding cells in excel Loop through cells and return only unique values trevorc Excel Programming 4 10-11-2018 03:49 PM
Excel Match return different row number kimak Excel 1 03-01-2017 09:39 AM
How to match one list with another and return certain values from corresponding cells in excel Compare cells in 2 columns in separate WBs and return data in Col 38-40 if there is match krispykreme Excel Programming 1 09-09-2016 11:25 AM
How to match one list with another and return certain values from corresponding cells in excel Excel Formula: return a range of cells that match tinfanide Excel 4 08-30-2014 07:03 AM


All times are GMT -7. The time now is 11:07 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft