#1
|
|||
|
|||
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… |
#2
|
|||
|
|||
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)) Copy the formula to whole datarange of your final table! And format the datarange there as date! |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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! |
#5
|
|||
|
|||
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 😄. I appreciate your help (I’m envious of people who understand excel on higher levels). |
#6
|
|||
|
|||
It worked!!! Although you’re not surprised. It helped me a lot, so thank you 😊
|
#7
|
||||
|
||||
You can use a combination of Excel functions such as COUNTIFS, IF, AND, and MIN to extract the required information.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
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 |
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 |
Is this possible using the Vlookup or any other function? | Delson | Excel | 4 | 02-08-2010 01:27 PM |