Need help with a challenging lookup/match type of situation

Is that a typo and D4 on Data should be CA

IF you made a mistake at Row 18:

=IF(C4&D4="RICHMONDCA",3,IF(A4&B4&C4&D4="CHICAGOILSANBERNARCA",2,IF(A4&B4="CHICAGOIL",1,IF(A4&B4&C4&D4="BIRMINGHAALELMIRAGEAZ",6,"N"))))

Row 18 is correct as shown in the example. In reality it's possible to match to multiple combinations. The "expected" results assume that if a row matches more than one combination the following order is used to determine the match:

1) Most criteria matched Ex: CHICAGO IL - SANBERNAR CA trumps just origin of CHICAGO IL
2) Order on the list: CHICAGO IL trumps RICHMOND CA because it's above it on the criteria selection order (ie. it's matchline ref is number #1 vs #3)

As for the formula, there are many options for each drop down so it might not be "CHICAGO IL" every time. In fact, a CITY/ST combo isn't always going to be used. So tying it to a specific location like "CHICAGO IL" won't work if the user wants to select just ORIGIN ST "TX" and no City, or a different CITY "BIRMINGHAM" and ST "AL".

Hope that makes sense.

This rearrangement of Glenn's formula yields the results that were manually placed into column E on the DATA sheet:

Formula:

Please Login or Register  to view this content.

I feel that in order for us to help with the scenario where there might only be a state selected, we will need to see another sample with expected results and perhaps corresponding explanations.
Let us know if you have any questions.

Sure I added a couple of examples to this one of just state. At a minimum I'd like the lookup to not exclude a row of data because a City or st is blank in the criteria options because the user didn't want to manually select every column. In the actual data there will be up to 9 criteria columns that the user can choose to select a value to use as a criteria filter (ex Store ID, model #, product #, Month, Year, Origin City, Origin ST, dest City, Dest ST, etc). So they may select any number of filters and combinations. So ignoring the blanks in the lookup will allow them to do that.

So for example.

1) A user selects only IL as their origin ST criteria.
2) Lookup won't assume CHICAGO IL isn't a match in the data row because user left Origin City blank.
3) Lookup won't assume JOLIET IL to DALLAS TX isn't a match because the user left Dest CITY and ST blank.
4) Lookup will exclude HOUSTON TX to MIAMI FL because none of the data fields match any of the criteria selections of the user

If I can just get to that point that is fine because I can direct the user to order their criteria from most filtered to least, but it needs to look at multiple rows of criteria combinations independently, so it can't be as simple as IF (CRITERIA!A5="" then look at CRITERIA!B5... because user may have selected an origin City in CRITERIA!A6 for a different combination of matching criteria.

But ideally, I'd like the formula to match from most complete to least complete. So if a user selects 3 different set of criteria in the CRITERIA TAB

1) User selects only ORIGIN CITY ST of CHICAGO IL as criteria row 1
2) User selects only ORIGIN CITY ST of CHICAGO IL and DEST CITY ST as SANBERNAR CA as criteria row 2
3) User selects only DESTIN ST of CA as criteria row 3

The the formula will match a row in the DATA tab of CHICAGO IL to SANBERNAR CA as 2 because it's the most complete match even though it matches all 3 in one way or another. So the hierarchy would be:

1) Match to 2 because it the Orig City ST and Dest City ST match criteria selections of CHICAGO IL and SANBERNAR CA (most complete)
2) Match to 1 because the origin CITY ST matches CHICAGO IL (less complete but still a match)
3) Match to 4 because the destination ST matches to CA (least complete, but still a match)

Hope this helps. The more I write the more I think this is too complex for excel which is why I'd be good with just not excluding a match because there is a blank in a given criteria column.