if cell shows a name, then use value for that name to add to formula

[Harley78]

I am stuck. Trying to figure out the following:

I have a cell O3 (469 rows) with a formulas =IFERROR(SUM($N3+10,0),"TBD") so on down the rows. I currently go down the list and change manually the number in red to reflect the correct transport times based on which port in CELL R.

I am trying to figure out how to AUTOMATE the formula if.

R3 shows Los Angeles then formula would be =IFERROR(SUM($N3+40,0),"TBD")
R3 shows New York then the formula would be =IFERROR(SUM($N3+30,0),"TBD")
and so on.

What is the best way to create this formula. Any help, tips etc., would great be appreciated.

Currently I have the port with transit times of in another worksheet:

A B
1 Los Angeles 40
2 New York 30
3 Savannah 14
4 Charleston 10

 

[sheetspread]

vlookup

 

[Harley78]

vlookup

Thanks sheetspread.

My problem is,,,,, how to incorporate the formula =IFERROR(SUM($N3+40,0),"TBD") so it adds 40 to the port date in N3 using Vlookup. Or are you saying to replace the 40 with a Vlookup formula?

 

[sheetspread]

Or are you saying to replace the 40 with a Vlookup formula?

Yes if I understand your requirements

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Fluff]

Thanks for updating your profile.

Currently I have the port with transit times of in another worksheet:

What is the name of that sheet & in which cells is that data in?

 

[Harley78]

vlookup

Don't know why I didn't think of doing that, but it worked. Thanks for the tip

=IFERROR(SUM($N2+VLOOKUP($R2,'TRANSIT DAYS'!$A:$B,2,FALSE)),"TBD")

 

[Fluff]

Glad you sorted it & thanks for the feedback.