Lookup with more than one criteria + suggestion of possible values

English
,
#1

Hi everyone
I would like to provide City suggestion to the spreadsheet user.

This suggestion is based on two criteria (on a same row) :

  • the country (via the Country iso code)
  • the zipcode

Since the user provides those two information, I want, in a third cell to give him or her suggestions for a City (It is possible to have many City for a same Zip code).

Bonus: if user edits country / zip code I want to empty or invalidate the city if there is no known match.

In another sheet (in the same file), I have a table witch acts as a reference with all country iso, zip code and cities.

Any idea how to do this ?

Thanks by advance

#2

This is contrary to the basic concept of a spreadsheet - “each cell is responsible only for its own content and does not affect any other cell.” Therefore, the built-in tools cannot clear the wrong value, you have to use a macro. But maybe you will be satisfied with simply painting the erroneous value with a bright red color? This is what Conditional Formatting can handle. And yes - there are several solutions to your problem. Can you provide a sample of the data on which they can be demonstrated?

#3

Hi, thanks a lot for your answer. I agree that using conditional formatting is enough for me. If the cell is not cleared automagically it is ok

Here is an example : demo.ods (78.4 KB) (I’ve stripped down the tab “ref_addresses” because the file was too big (there was ~60k lines in there - built using geonames dataset)

#4

One of the possible solutions looks like this - demo.ods (74.5 KB)

First of all I changed the named range ISOCODES, took these values from the ref_adresses sheet. To avoid duplicates in the drop-down list, select the Sort entries ascending checkbox.

For the zipcode drop-down list, not the entire range of codes is used, but the formula IF(ISOCODES=A2;ZIPCODES;""). The Sort entries ascending checkbox is also checked.

The rest of the work will be done by the formula
=IFERROR(OFFSET(ref_adresses.$C$1;SUMPRODUCT(MAX((ISOCODES=A2)*(ZIPCODES=B2)*ROW(ZIPCODES)))-1;0);"Choose correct country and zipcode")

1 Like
#5

wow this looks impressive @JohnSUN thanks a lot !

#6

It works fully as I expected. I think I’ll have a city cell configured the same way than you did for the zip code and add another column to check the whole integrity using the formula inspired by the one you have written in your comment.

Thanks a lot

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.