Help with Excel formula to look up text in multiple columns then retrieve a value with a formula from another column.

Can someone please help me with a formula?  I'll try to verbalize what I need to do, although sometimes it's hard to be clear when spelling it out.

In a workbook with multiple sheets, there is info on sheet 1 that I need to match/look up using info from sheet 2.  If I find a match in 1 or more of the 3 columns of sheet 1 - on the same row - then I need to retrieve and manipulate the value from another column in sheet 1 and place it on sheet 2.

I'm attaching a sample worksheet.  Below is what I wrote on sheet 2, column B.  I hope this makes sense.

"If Name is found in Sheet 1 column A, or B or C, then place the sheet 1 column D amount divided by 3 here, else if the Name is found in two of the three columns (same row), sheet 1 column A,B, C, then place the sheet 1 column D amount times 2/3 here, else if Name is found in all three columns (same row) of Sheet 1 columns A, B, C, then place the full amount of D here."

I've been trying combinations of Ifs, nested if, with vloouup or with index/match but I haven't hit on the right combination that makes this work.

Thanks in advance for your help!