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'...
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.
- Trying this now. Thank you so much!!!! Will let you know how I go!.
