Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-17-2023, 04:44 AM
Agnieszka Agnieszka is offline Looking for vlookup style function for multiple values Mac OS X Looking for vlookup style function for multiple values Office 2016 for Mac
Novice
Looking for vlookup style function for multiple values
 
Join Date: Apr 2023
Posts: 15
Agnieszka is on a distinguished road
Default Looking for vlookup style function for multiple values

Hi everyone, hope someone can help me. I need to find multiple returns for multiple subjects. Original table looks like this:
Column A - names of students (100+ with same names repeated few times)


Column B - names of courses (for example A - Z)
Column C - course submission date

I need to find out if all students did course B, D, H, O and X, and when.

Final table looks like:
Column A - names of all students
Raw 1 names of courses - 1b course B, 1c - course D…
Cells in between would search for course submission date…
Reply With Quote
  #2  
Old 04-17-2023, 08:42 AM
ArviLaanemets ArviLaanemets is offline Looking for vlookup style function for multiple values Windows 8 Looking for vlookup style function for multiple values Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Into cell B2 of your final table enter the formula
Code:
=IIF(SUMIFS(SourceDateRange, SourceNameRange, $A2, SourceCourseRange, B$1)=0,"",SUMIFS(SourceDateRange, SourceNameRange, $A2, SourceCourseRange, B$1))
, where all 3 dateranges are of same dimension, and defined using absolute references (like SourceSheet!$A$2500 for SourceNameRange, and SourceSheet!$B$2500 for SourceCourseRange, etc.).
Copy the formula to whole datarange of your final table! And format the datarange there as date!
Reply With Quote
  #3  
Old 04-17-2023, 09:05 PM
Agnieszka Agnieszka is offline Looking for vlookup style function for multiple values Mac OS X Looking for vlookup style function for multiple values Office 2016 for Mac
Novice
Looking for vlookup style function for multiple values
 
Join Date: Apr 2023
Posts: 15
Agnieszka is on a distinguished road
Default

Hi ArviLaanemets, thank you for your reply.
My excel doesn’t seem to have IIF function. Is there other one I can use? I did try with IF, but didn’t get anywhere.
Reply With Quote
  #4  
Old 04-17-2023, 10:17 PM
ArviLaanemets ArviLaanemets is offline Looking for vlookup style function for multiple values Windows 8 Looking for vlookup style function for multiple values Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Sorry! Of course the function must be IF()!

Did you try the SUMIFS() part - without checking for zero sums. It will return 0 for cases, where no date was entered for course for this student, and when formatted as date, it will be displayed as date like 0.01.1900. This my be confusing, so I added the IF() part.

When this will not work, my 1st quess will be, the dates in your SourceDateRange aren't dates at all, but datestrings.

Btw, I add an example!
Attached Files
File Type: xlsx SumifsDatesExample.xlsx (11.5 KB, 4 views)
Reply With Quote
  #5  
Old 04-17-2023, 10:55 PM
Agnieszka Agnieszka is offline Looking for vlookup style function for multiple values Mac OS X Looking for vlookup style function for multiple values Office 2016 for Mac
Novice
Looking for vlookup style function for multiple values
 
Join Date: Apr 2023
Posts: 15
Agnieszka is on a distinguished road
Default

I’ll try it tomorrow when I’m back at my spreadsheet.
If I can’t type it properly I’ll paste my data into your example and that should work (will make sure dates are dates &#128516.
I appreciate your help (I’m envious of people who understand excel on higher levels).
Reply With Quote
  #6  
Old 04-18-2023, 05:21 PM
Agnieszka Agnieszka is offline Looking for vlookup style function for multiple values Mac OS X Looking for vlookup style function for multiple values Office 2016 for Mac
Novice
Looking for vlookup style function for multiple values
 
Join Date: Apr 2023
Posts: 15
Agnieszka is on a distinguished road
Default

It worked!!! Although you’re not surprised. It helped me a lot, so thank you 😊
Reply With Quote
  #7  
Old 04-19-2023, 10:38 PM
Martha's Avatar
Martha Martha is offline Looking for vlookup style function for multiple values Windows 11 Looking for vlookup style function for multiple values Office 2010
Novice
 
Join Date: Apr 2023
Posts: 2
Martha is on a distinguished road
Default

You can use a combination of Excel functions such as COUNTIFS, IF, AND, and MIN to extract the required information.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Looking for vlookup style function for multiple values Keeping the source format when extracting data from multiple cells using Vlookup function praboos2001 Excel 6 11-15-2016 04:40 AM
Multiple found values from five sheets, multiple returned values in sheet six? irisha Excel Programming 26 09-30-2016 01:20 AM
Looking for vlookup style function for multiple values VLookup multiple values and Sum tinfanide Excel 2 09-02-2014 11:41 AM
Using vlookup with the IF function CSzoke Excel 11 06-01-2013 10:56 PM
Looking for vlookup style function for multiple values Is this possible using the Vlookup or any other function? Delson Excel 4 02-08-2010 01:27 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:48 AM.


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