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!