What is the formula?

[pakplan11]

I want to know the formula for the following example;


I want to be able to select the following in the range of M2 through M30

[TABLE="width: 172"]
<tbody>[TR]
[TD]100%:[/TD]
[/TR]
[TR]
[TD]90%:[/TD]
[/TR]
[TR]
[TD]80%:[/TD]
[/TR]
[TR]
[TD]70%:[/TD]
[/TR]
[TR]
[TD]60%:[/TD]
[/TR]
[TR]
[TD]50%:[/TD]
[/TR]
[TR]
[TD]40%:[/TD]
[/TR]
[TR]
[TD]30%[/TD]
[/TR]
[TR]
[TD]20%[/TD]
[/TR]
[TR]
[TD]10%:[/TD]
[/TR]
[TR]
[TD]10%:[/TD]
[/TR]
[TR]
[TD]0%[/TD]
[/TR]
</tbody>[/TABLE]

If i select any of the %'s from MY COLUMN M above, I want the following to auto populate in N2 and O2 respectfully;

[TABLE="width: 1056"]
<tbody>[TR]
[TD]100%:[/TD]
[TD]WON[/TD]
[TD]Customer has sent PO and PCM-G has processed[/TD]
[/TR]
[TR]
[TD]90%:[/TD]
[TD]Decision[/TD]
[TD]AE has met with ALL decision makers and waiting on PO. Proposal in purchasing[/TD]
[/TR]
[TR]
[TD]80%:[/TD]
[TD]Decision[/TD]
[TD]Paperwork, proposal through legal, negotiating complete, going to purchasing[/TD]
[/TR]
[TR]
[TD]70%:[/TD]
[TD]Decision[/TD]
[TD]Completed paperwork process and any outstanding technology issues, pricing, paperwork in legal.[/TD]
[/TR]
[TR]
[TD]60%:[/TD]
[TD]Decision[/TD]
[TD]Selected PCM-G and technology proposed, begin negotiations of pricing,[/TD]
[/TR]
[TR]
[TD]50%:[/TD]
[TD]Evaluation[/TD]
[TD]Evaluation stage. How is the decision made to purchase?[/TD]
[/TR]
[TR]
[TD]40%:[/TD]
[TD]Proposal[/TD]
[TD]Quote created, pricing and timeframe determined. Evaluation established including expectation of timeframe and next steps.[/TD]
[/TR]
[TR]
[TD]30%[/TD]
[TD]Qualifying[/TD]
[TD]NSP-Deal Registration is approved by the MFG[/TD]
[/TR]
[TR]
[TD]20%[/TD]
[TD]Qualifying[/TD]
[TD]1) AE completed meeting or conference call 2) AE Identifed project is budgeted 3) AE Identified purchasing process (what has to happen internally at the customer to obtain a PO) 4) AE has identified legal contacts and procurement contacts for succesful close 5) AE has contacted MFG partner for deal registration and opportunity[/TD]
[/TR]
[TR]
[TD]10%:[/TD]
[TD]Prospecting[/TD]
[TD]Meeting or conference call scheduled, purpose is to identify requirements through a round table or white board discussion.[/TD]
[/TR]
[TR]
[TD]10%:[/TD]
[TD]Prospecting[/TD]
[TD]A lead, cold call attempt, vendor lead, general interest in a project.[/TD]
[/TR]
[TR]
[TD]0%[/TD]
[TD]Lost[/TD]
[TD]Lost[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 884"]
<tbody>[TR]
[TD]In the end M = the the percentage column, N= the WON, Decision, Qualifying etc column and O = the text description


THANKS FOR YOUR HELP!~!![/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Set up a lookup table and use VLOOKUP.
See: https://www.techonthenet.com/excel/formulas/vlookup.php