Auto populate cell price based on text in another cell

Dear @Tyler Lowe,

Good day, and thank you for reaching out to the Q&A Forum. I understand that you’d like column AB (Price) to automatically populate based on the values entered in column AA (OD/OS/OU), and for column AC (Total RVS Reimbursement) to be highlighted when it does not match the corresponding value in column AB.

Please follow the steps below to set this up:

1.Auto-populate the price in column AB (using an IF formula)

Assuming your data starts in row 2 (adjust as needed):

In cell AB2, enter the following formula: =IF(AA2="OU", 890, 445)

Drag the formula down for all rows.

This will automatically set:

• OD or OS > 445.00
• OU > 890.00

2.Apply conditional formatting to column AC

To highlight cells in column AC when they do not equal the corresponding value in AB:

• Select the range in column AC where your data resides (e.g., AC2:AC100).
• Go to Home tab > Conditional Formatting > New Rule.
• Choose Use a formula to determine which cells to format.
• Enter this formula: =AC2 <> AB2
• Click Format > Go to the Fill tab and choose a highlight color (e.g., light red or yellow).
• Click OK twice to apply.

Now any cell in AC that does not match the price in AB will be automatically highlighted.

I hope this information is helpful. Please follow these steps and let me know if it works for you. If not, we can work together to resolve this. Thank you for your patience and understanding. If you have any questions or need further assistance, please feel free to share them in the comments so I can continue to support you. I'm looking forward to your reply.

If the answer is helpful, please click "Accept Answer" and kindly upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.