V lookup value from multiple columns

Question

I have to look up the value from two columns (I.e. Record ID Type I & II) to get the desired result. I won't be able to do that with the help of Find & Replace tool because it will find the value from one column only.

Input :-

Record ID -Type IRecord ID -Type IIScienceMaths English255005060355050005565457560006585651008000709575

Output :-

Record IDScienceMathsEnglish25   50   5000   6000

I have attached my input & output file for your reference. Please help me with relevant solution

Lookup Value.xlsx

kelsey_kincaid · Answer

Hi @Vicky92 ,

You can use a formula tool to combine the two fields (e.g. [Record ID - Type 1] + [Record ID - Type 2] ) in both data sets and then do the Find/Replace based on that field. Then you can drop your combined field in the final output. Alternatively, you can use a join tool and add both Record ID - Type 1 and Record ID - Type 2 as unique keys in the JOIN tool.

271828 · Answer

It would be helpful if the excel file included the formula(s) you're using. I don't understand what logic you need with the current attachment.

Could you simply aggregate the 2 record ID columns with a formula tool and then use that as your lookup?