Forum Discussion
Performing a lookup where the lookup value appears multiple times (incrementally through rows)
I have been puzzling over the following.
Have a table that looks a bit like this:
What I want to do is populate. second worksheet and record Module 1, 2, and 3 completions, by entering a '1' in a column if the value ModuleCompleted value I look for is found.
E.g.
I can manage to use an XLOOKUP to get the first value in (well it enters 'Module 1'), but how on earth do I get the other ones into M2, M3 columns?
=XLOOKUP([@Code],ReportOutput!A:A,ReportOutput!D:D,"")The above will return "Module 1" in M1 column, however, how do I get the lookup to find the 'next' Code that matches in the first sheet?
I tried using INDEX but am at a loss. Any help or hints in the right direction are MUCH appreciated.
Thanks so much.
5 Replies
in J3 and copy right:
=IF( COUNTIFS(Data[[Code]:[Code]],Report[@[Code]:[Code]], Data[[ModuleCompleted]:[ModuleCompleted]],Report[[#Headers],[Module 1]] ), 1, "Not completed" )Red_Handler Hi everyone, am still puzzling on this one. And am wondering if the approach is wrong that I am looking at.
The main thing I need to check and verify with the original data is that someone has completed Module 1, Module 2 and Module 3.
If they have completed all three then I need to add them to a separate table for reporting purposes.
Is there any way to do this?
- Alternatively, is there a way in which I can turn what is four separate rows into a single row? E.g. the report creates a new row for each component that is completed.
=IFNA(IF(AND(MATCH(F2&"Module 1",$A$2:$A$14&$D$2:$D$14,0),MATCH(F2&"Module 2",$A$2:$A$14&$D$2:$D$14,0),MATCH(F2&"Module 3",$A$2:$A$14&$D$2:$D$14,0)),"completed Module 1,2 and 3",""),"not completed")You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021.
