Choosing between to cells to populate

You need three things set up in your Sales table before the formula can work.

Step 1: Link Sales to Products

Add a new field in Sales, choose "Link to another record", select your Products table. Make sure each sale record has the correct product selected.

Step 2: Add two Lookup fields

Add a new field, choose "Lookup", select your Product link field, then pick AU/NZ Rate. Repeat for ROW Rate. You'll now see both rates from the linked product sitting directly in the Sales table.

Step 3: Add a Formula field

IF(Region="AU/NZ", {AU/NZ Rate (from Product)}, {ROW Rate (from Product)})

When Region is AU/NZ it returns the AU/NZ rate. Everything else returns the ROW rate.

The formula logic you're thinking of is correct. It just needs the lookup fields to exist first so it has something to reference.

Have a look at the attached screenshots:

Thankyou,

When you link a table is it possible to get it to automatically upload the value or do I have to manually select the link, 

for example in Products each Item has an Item number - in the Sales all I have is the item number, region and qty, can the link table field in sales automatically select the right product based on the same Item number?

​@Portman You have 3 options:

1. Build an automation to find product with given item number and link it
2. Make the item number itself as a linked record field in Sales table and ensure that primary field in Product table is also item number. This way when you populate item number in Sales item number field, it will get linked automatically.
3. Use Deep Match field for Product in Sales table. Deep Match uses AI to identify the right record to link to. Ask it to link on basis of item number.