VLOOKUP - want empty lookup result to allow free form typing

hmcnamara95571

Hello,

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...

LOCATION SEARCH LOCATION RESULT

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!

Heather

Andrée Starå

Hi @hmcnamara95571

Unfortunately, it's not possible at the moment, but it's an excellent idea!

Please submit an Enhancement Request when you have a moment

As a possible workaround, you could maybe use a helper column for the new value instead or set up a structure in the sheet so the formula auto-fill still would work (add to the top and then move).

Would any of those options work?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

TAbs

Has there been any addition of this option since 2020, as this is exactly what I need as well?