I have a spreadsheet in Excel that kinda looks like this:
| SubjectID | ValidQuestions | Name |
|---|---|---|
| XXX000 | 10 | Python |
| CCC111 / TTT222 | 9 | Data Structure |
. . The first column represents the ID code that identifies a certain subject. The second one is the number of valid questions that can be used in a exam. The third one is the name of the subject.
I have another tab that kind looks like this:
| SubjectID | ValidQuestions |
|---|---|
| XXX000 | |
| CCC111 |
But this time, the SubjectId column contains only one value that is not separated by a slash and the ValidQuestions column is empty. I need to fill the second one with values from the first tab. I tried to use VLOOKUP but it's not working. I would appreciate help.
CodePudding user response：
Use VLOOKUP() with 
• Formula used in cell F2
=VLOOKUP(E2&"*",$A$2:$C$3,2,0)
You can also use INDEX() & MATCH() with ISNUMBER() & SEARCH()
• Formula used in cell F2
=INDEX($B$2:$B$3,MATCH(TRUE,ISNUMBER(SEARCH(E2,$A$2:$A$3)),0))
CodePudding user response：
If you are on Microsoft-365, then I would recommend to use XLOOKUP().
=XLOOKUP("*"&B9&"*",$A$2:$A$3,$B$2:$B$3,,2)
To make it dynamic spill array use XLOOKUP() with BYROW() function.
=BYROW(B9:B10,LAMBDA(x,XLOOKUP("*"&x&"*",$A$2:$A$3,$B$2:$B$3,,2)))
MAP() could be another alternate. Use-
=MAP(B9:B10,LAMBDA(x,XLOOKUP("*"&x&"*",A2:A3,B2:B3,,2)))
