Return a value based on 3 criteria with specific content in a cell

[Emma Eve]

Hi Everyone.

I need help with an IF formula. Normally I can do these but this one is a bit more complicated and my boss is wanting a solution ASAP. UGH! Anyways...
What I need:
I need the RETURN VALUE cell in Workbook 1 l to be populated with the Monthly Run - Territory in Workbook 2 amount for each Representative in Workbook 1.

There are 3 criteria that I believe are necessary to make this happen
1. The Rep name.

2. The Month - this will change each period by adding new data into the respective columns in Workbook 2. So when Workbook 1's FEB changes to MAR I need it to pull the MAR column data from Workbook 2.

3. Workbook 2's dollar amount for Monthly Run - Territory. The issue here is the words after Monthly can change for other Representatives. For example a Rep could have Monthly Run - Domestic, or Monthly - Asia.

Sorry for the terrible looking chart. I have no idea how to post something better.

Thank you so much for the help! Have a wonderful day!

Emma

[TABLE="width: 500"]
<tbody>[TR]
[TD]Workbook 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl66, width: 108, align: center"]FEB[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl66, width: 108, align: center"]Color Code[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl66, width: 108, align: center"]Rep[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl66, width: 108"] RETURN VALUE [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl66, width: 108"]Representative 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl66, width: 108"]Workbook 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl66, width: 77"]FEB[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl66, width: 108"]Representative 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl66, width: 77"]12[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl66, width: 108"]Run 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl66, width: 77"] $ 8,535.56 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl66, width: 108"]Part Run 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl66, width: 77"] $ 3,541.80 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl66, width: 108"]Ending Run 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl66, width: 77"] $ 4,993.76 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl66, width: 108"] Deficit 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl66, width: 77"] $ -[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl66, width: 108"] Deficit 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl66, width: 77"] $ -[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl66, width: 108"] Cumulative Deficit[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl66, width: 77"] $ -[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl66, width: 108"]Monthly Run - Territory[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD](RETURN VALUE >>>)[/TD]
[TD]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl66, width: 77"] $ 4,993.76 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl66, width: 108"]YTD Run[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl66, width: 77"] $ 4,993.76 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl66, width: 108"]Prior Month Run[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl66, width: 77"] $ 70,944.18 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl66, width: 108"]YTD Run[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl66, width: 77"] $ 70,944.18 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

show us A1 to T20 of workbook 1 and 2

highlight, copy, goto reply box here, paste

 

[Special-K99]

This is such a terrible layout it's better to post a file.

Unfortunately...

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images as you've found.

https://www.mrexcel.com/forum/about-board/508133-attachments.html

Upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

 

[Emma Eve]

I'm sorry but this is the best I can do. I know this is just as bad. I do not have permission on this PC to implement the methods supplied in the link below. I may just need to tell my boss I cannot do it.

[TABLE="width: 606"]
<tbody>[TR]
[TD]In Workbook 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FEB[/TD]
[TD]Color Code[/TD]
[TD]Rep[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] RETURN VALUE[/TD]
[TD][/TD]
[TD]Representative 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]In Workbook 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]<c></c>[/TD]
[TD]FEB[/TD]
[TD]MAR[/TD]
[TD]<d></d>[/TD]
[/TR]
[TR]
[TD]Representative 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Run 1[/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 8,535.56[/TD]
[TD] $ -[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Part Run 1[/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 3,541.80[/TD]
[TD] $ -[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Ending Run 1[/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 4,993.76[/TD]
[TD] $ -[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Deficit 1[/TD]
[TD][/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Deficit 2[/TD]
[TD][/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"] Cumulative Deficit[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Monthly Run - Territory[/TD]
[TD][/TD]
[TD] $ 4,993.76[/TD]
[TD] $ -[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]YTD Run[/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 4,993.76[/TD]
[TD] $ -[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Prior Month Run[/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 70,944.18[/TD]
[TD] $ -[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]YTD Run[/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 70,944.18[/TD]
[TD] $ -[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
What I need:
I need the RETURN VALUE cell in Workbook 1 l to be populated with the Monthly Run - Territory in Workbook 2 amount for each Representative in Workbook 1.

There are 3 criteria that I believe are necessary to make this happen
1. The Rep name .

2. The Month <c> - this will change each period by adding new data into the new Month's column <d> in Workbook 2. So when Workbook 1's FEB changes to MAR I need it to pull the MAR column data from Workbook 2.

3. Workbook 2's dollar amount for Monthly Run - Territory </d></c>. The issue here is the words after Monthly can change for other Representatives. For example a Rep could have Monthly Run - Domestic, or Monthly - Asia so I think the formula just needs to see Monthly as a criteria. Additionally, the Monthly Run row is not always in the same position in the reconciliation. Depending on the Representative, it could be a few rows up or down from what is shown above.

 

[oldbrewer]

you can copy A1:D3, come into the reply box here, and paste

once we understand what you are trying to do, it will be solved

explain it in tiny bits, we solve it bit by bit

consider google sheets or similar

 

[Emma Eve]

Sorry I have taken a while to respond. I have created a Google doc with my question in Excel format. I hope this makes things much easier. If I can provide any other clarity, please let me know!

https://docs.google.com/spreadsheets/d/1VPB23hsYr8SDyJ207LqrkdtP5cMPSKuJDn50yVpwvFQ/edit#gid=0