Using LOOKUP in a formula on a different table to use in a formula

[Lawrence_Ames]

Hello!

I cannot figure this out.

On one table, I have a table that lists the weights of a package on each row, and the cost to ship.

For example, a 1# pkg costs $12.00 to ship, and on the next row, a 2# pkg costs $12.90 to ship.

On a second table, I want to have a formula that takes an item's weight x qty, and display the cost to ship .

So the formula would be

[item wt] x [qty] = [total weight] and then display the cost to ship that TOTAL WEIGHT, and then lookup the cost to ship that weight in my first table.

so if I had a 1 lb item and I have a qty of 2, the LOOKUP would lookup in my first table that it would cost [$12.90] to ship this 2# order, which I would then use in a formula to calculate the total cost of the entire order (item cost )x(qty)+(ship cost).

I cannot figure out how to LOOKUP the SHIP COST ($12.90 in this example) and use it in a formula. Any help very much welcome!

Thank you,  Lawrence

[TheTimeSavingCo]

Could you provide access to an example base or screenshots of the relevant data?  It seems like the lookup would display the correct value as long as you've linked the correct record, but given that you're having issues with this I think I'm not understanding your setup

[Lawrence_Ames]

Thank you for looking into this.

OK, so the first table, attached screenshot, is a table of costs to ship various weights via UPS.

I want to use the correct value on a second table. So if the second table calculates a package that is, say, 10 lbs, I want it to lookup the shipping cost of a 10 lb package, and bring to the second table the cost to ship it, which I would then use in a formula.

Try as I might, I have been unable to do this.

Thank you for your help!

Lawrence

[TheTimeSavingCo]

Hmm, and you have a linked field in the second table to the "UPS Freight Rates" table, right?  Once you've added the weight of the package to said linked field, i.e. linked the two records together, the lookup field should populate, yes?

[Lawrence_Ames]

Yes. They are linked. I'm new to AT.

I want to pull data from one table to the other, but the data is, "For the 10# box, pull the UPS Rate to ship a 10# box. Table 1 has Weight and UPS Rate, and table 2 is where I want to use that data in a formula.

In Excel, it would be "VLOOKUP" to table 1, and put result in formula. But here? I'm at a loss.

But yes, I did link both together.Thank you!

[TheTimeSavingCo]

Interesting, it should work just fine then

Could you provide access to an example base so that I could assist further please? 

[Lawrence_Ames]

Thank you SO MUCH.

Here are my UPS Rates: https://airtable.com/shrDZwlHdv9UFXurQ

And here is the table I'm trying to poplulate with the correct weight. But I can't figure out how to lookup the correct weight bases on this table.

https://airtable.com/shrurZzpROg8gzvn3

Thank you, Lawrence

[TheTimeSavingCo]

Could you provide an invite link for that example base please?

[Lawrence_Ames]

Thank you. Just figured out how to do that!  Here you are. Thank you SO MUCH!

https://airtable.com/invite/l?inviteId=invcexAcuiaGbpljg&inviteToken=1793bb15ca6b0e380502a3db6fd3c60...

[TheTimeSavingCo]

Thanks! 

You already set up everything pretty perfectly actually.  Just need to copy and paste the value from "Total Weight" into the "UPS Freight Rates" linked field so that it'll display the correct lookup value:

To automate it, you can make an automation that'll trigger when the "Total Weight" field is not empty AND the "UPS Freight Rates" field is empty, and give it an "Update Record" action to paste the "Total Weight" value in for you, and I've created that automation in the base below for you to check out!

Link to base