troubleshooting Question

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

Avatar of fabi2004
fabi2004Flag for United States of America asked on
Microsoft OfficeMicrosoft ExcelMath / Science
9 Comments1 Solution198 ViewsLast Modified:
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!
ASKER CERTIFIED SOLUTION
Shums
Excel VBA Developer
Join our community to see this answer!
Unlock 1 Answer and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros