Conditional Price Match

[jyeung753]

Hello, 

It would be greatly appreciated if anyone could help advise on the inquiry below. 

I am running a workflow that requires certain prices to match based off an investment name. An example of this being, I have a client price and an internal (company provided) price. If the investment name in column A, were to be "Red Hill Biotics", then column B (client price) with a price of $90 should match column C's price (company provided) which initially returns as N/A. 

The main goal here is to essentially have an N/A cell, return an equal value to the dollar amount reported in another column based off which investment it was booked for. 

I have tried using the formula tool but have not found any luck in creating a working formula without returning parse errors or broken expressions. 

Sample formula below:

IF[NEW_InvestmentName2], "Red Hill Biotics" THEN [NEW_ Primary Price] = [NEW_Client Price]

Apologies if this was not explained clearly. 

Thanks!

[BrandonB]

The formula should first be set to either update the Client Price column, or create a new column. Then you should do something like this:

IF [Client Price] = "N/A" AND [InvestmentName] = "Red Hill Biotics"

THEN [Primary Price]

ELSE [Client Price]

ENDIF

Let me know if this is your goal. You needed to fully form your IF statement which was one issue, but also needed to fully establish your conditions. To follow up here though, if this is actually a Lookup in Excel rather than setting Client Price to Primary Price in certain scenarios, this is actually a matter of joining the two data sets using a join tool rather than a formula. 

If you wouldn't mind providing your data and expected output, we could help to mock up a solution. Please remove anything sensitive/confidential as this is a public forum. 

[jyeung753]

Hi Brandon, 

Thank you for the initial response! The goal is indeed to set the primary price to the client provided price based off which investment it is placed for. 

Please see the attached file, I left only the necessary details for this case. 

In additional to my first inquiry - would there be a method to expand on the formula to detect any security with "Red Hill" in the "investment name" cell to automatically match with the corresponding client provided price? I failed to mention there are different types of a security we provide pricing for.

I provided an example of what the varied investment names would look like. 

Please let me know if any additional material is necessary.

Thank you!

[BrandonB · Accepted solution]

Yep! Just a quick tweak using contains rather than an exact match. 

IF [Client Price] = "N/A" AND Contains([InvestmentName], "Red Hill Biotics")

THEN [Primary Price]

ELSE [Client Price]

ENDIF