Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!
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!
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!
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
ack! sorry! Here it is.
Book1.xlsx
Book1.xlsx
oh no...I need to clarify something else...
the "name" might appear more than once in any of the three columns of sheet 1. If so, then the amounts should be added (after they are calculated as times 1/3 or 2/3 or full amount).
the "name" might appear more than once in any of the three columns of sheet 1. If so, then the amounts should be added (after they are calculated as times 1/3 or 2/3 or full amount).
Hi,
I tried something manual (of course with formula).
For eg: Gabe appeared in all three columns in 2nd row. Amount 1 should be 1000 plus, he/she appeared once in Col B & 2nd Row, from 1500/3=500. Gabe's total for Amount 1 should be 1500.
Same way for Amount 2, he/she appeared in all three columns in 2nd row. Amount 2 should be 2000 plus, he/she appeared once in Col B & 2nd Row, from 2500/3=833.33. Gabe's total for Amount 2 should be 2833.33
Like wise I did for all the names, but Total is not distributing evenly.
Please check sample.
Book1_v1.xlsx
I tried something manual (of course with formula).
For eg: Gabe appeared in all three columns in 2nd row. Amount 1 should be 1000 plus, he/she appeared once in Col B & 2nd Row, from 1500/3=500. Gabe's total for Amount 1 should be 1500.
Same way for Amount 2, he/she appeared in all three columns in 2nd row. Amount 2 should be 2000 plus, he/she appeared once in Col B & 2nd Row, from 2500/3=833.33. Gabe's total for Amount 2 should be 2833.33
Like wise I did for all the names, but Total is not distributing evenly.
Please check sample.
Book1_v1.xlsx
Please find attached Manual working in Sheet1 & Formulated in Sheet2 (with helper columns in Sheet1)
Book1_v2.xlsx
Book1_v2.xlsx
Hi Shums, thank you for all this work.
I hate to ask more questions, but I've been staring at it for almost an hour and can't be sure I understand.
1. Will this expand out to greater number of names and greater number of rows? For example, what if there were 15 different names and 300 rows?
2. I need to minimize any changes/additions we make to sheet 1. I might get away with an extra hidden column or two, but I'd rather add the helper columns to sheet 2 if at all possible. So, if I try to move your additional columns from sheet 1 to sheet 2, do I have to add a row to sheet two for each row in sheet 1?
3. Do we need the count/average columns? The amounts are always going to be divided by 3 and then multiplied by then number of times a name appears in columns A, B or C. I need to keep a running total of each amount for each person but I won't necessarily know how many times that persons name appears in sheet 1 so I can't simply use rows to track that running total. How would I take that into account? On this example it was a straightforward 3 rows for each person.
I hate to ask more questions, but I've been staring at it for almost an hour and can't be sure I understand.
1. Will this expand out to greater number of names and greater number of rows? For example, what if there were 15 different names and 300 rows?
2. I need to minimize any changes/additions we make to sheet 1. I might get away with an extra hidden column or two, but I'd rather add the helper columns to sheet 2 if at all possible. So, if I try to move your additional columns from sheet 1 to sheet 2, do I have to add a row to sheet two for each row in sheet 1?
3. Do we need the count/average columns? The amounts are always going to be divided by 3 and then multiplied by then number of times a name appears in columns A, B or C. I need to keep a running total of each amount for each person but I won't necessarily know how many times that persons name appears in sheet 1 so I can't simply use rows to track that running total. How would I take that into account? On this example it was a straightforward 3 rows for each person.
For all your 3 questions, answer is just one formula as below:
For Amount1:
Please find attached...
Hope this helps
Book1_v3.xlsx
For Amount1:
=ROUND(SUMIFS(Sheet1!$D:$D,Sheet1!$A:$A,Sheet2!$A2)/3+SUMIFS(Sheet1!$D:$D,Sheet1!$B:$B,Sheet2!$A2)/3+SUMIFS(Sheet1!$D:$D,Sheet1!$C:$C,Sheet2!$A2)/3,2)
=ROUNDDOWN(SUMIFS(Sheet1!$E:$E,Sheet1!$A:$A,Sheet2!$A2)/3+SUMIFS(Sheet1!$E:$E,Sheet1!$B:$B,Sheet2!$A2)/3+SUMIFS(Sheet1!$E:$E,Sheet1!$C:$C,Sheet2!$A2)/3,0)
Please find attached...
Hope this helps
Book1_v3.xlsx
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
That's fantastic. Thank you so much for all of your help. I was afraid I wasn't explaining my questions well but you figured out exactly that was needed to make this work. I very much appreciate your time and your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.