VLOOKUP - want empty lookup result to allow free form typing



I'm using this formula:

=VLOOKUP([Location Search]1, {Locations Range 1}, 2, false)

where [Location Search] refers to a "LOCATION SEARCH" column in the sheet collecting the data and {Locations Range 1} refers to my look-up table below. It returns the "LOCATION RESULT" column below and I need it to be exact...thus the "false".

It works fine except I need one of the lookup options to return a free form typing option in the return field. Eg of lookup table...


Springfield Elm 5555 5555 Elm St, Springfield, IA 55555

Orlando Simpson 7777 7777 Simpson Ave., Orlando, FL 77777

New Location

Santa Fe Maple 9999 9999 Maple St., Santa Fe, NM 99999

Where "New Location" returns an empty field, I would like it to return a field that allows the user to type in the address of the new location in the main sheet where the formula is located. Currently, what happens is it returns an empty cell but when the user types in the new location address, the formula is deleted from that cell which then stops the original formula from carrying down to new rows added below. I've tried using IF with the VLOOKUP with the same results.

Any ideas?

Thank you!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!