How to retrieve value from lookup for multivalue field

[NAGA4]

I have a lookup like this 

Name	Status	ExamID
John	Pass	123
Bob	Pass	345
John	Fail	234
Bob	Pass	235
Smith	Fail	231

My Events are having Name alone as the unique identifier.  

I wrote my query like this 

index=userdata [ inputlookup userinfo.csv | fields Name]  | lookup userinfo.csv Name as Name OUTPUT Status as Status ExamID as Identifier 

Via first subsearch I extracted the events only belong to names present in the table and then i tried to ouput the status and examid for those Names. On combination of these 3 in the event i need to evaluate fourth result. 

John - Pass - 123 ->> In this if ExamID falls between 120 and 125 I need to print value for fourth field as "GOOD" 

However while am printing output from lookup i got multivalues like this. Then i tried to do mvappend and that did not work correctly. 

So how to do this correctly

John

Pass Fail

123 234

[PickleRick]

Wait a second. What does it have to do with any events returned from the index? So far you're only operating on the data from the lookup.

Also, unless for displaying (but even then it's... a disputable practice), you don't want to merge values into multivalued fields this way. You'll effectively get two multivalued fields with no connection between them whatsoever. So if you wanted to sort one of them (for example to list passed exams before failed ones or vice-versa) you can't reorder the other field the same way. They are just two separate fields with multivalued contents but there is no relationship between those contents.

(and should any of those values prove to be empty, the whole field will "squash" so you will not have any spaces between values).

[NAGA4]

Thank you @PickleRick . Here is the detailed background of my requirement. I need to refer the values from lookup and compare it with values in events for same field and derive the other field

https://community.splunk.com/t5/Splunk-Search/Help-with-splunk-search-query/m-p/685039#M233782

[andrew_nelson]

Try something like this: 

<your search> ... 
| eval exam_result=mvzip(ExamID, Status, "~") 
| fields - ExamID Status
| mvexpand exam_result 
| eval ExamID=mvindex(split(exam_result, "~"), 0), Status=mvindex(split(exam_result, "~"), 1)
| eval extra_status = if(ExamID>=120 AND ExamID<=125 AND match(Status, "Pass"), "GOOD", null())

[deepakc]

Try this

| inputlookup userinfo
| eval fourth_result=if(ExamID>=120 AND ExamID<=125,"GOOD","OTHER")

[NAGA4]

I need to do this for multivalues which is not working.