VLOOKUP from a range matching multiple criteria

[merce333]

Hello. I've been struggling with this all morning. I had the following sheet that worked fine.
The user enters SUBJECT STATE, in this case KY for Kentucky.
Below there is a table called, "FullCompDataSet", with 3 columns.
I need to do 2 things from this data (output shown below in red):
A. Count the number of records in FullCompDataSet whose "ST/Prov" match the SUBJECT STATE. I had this formula:

=COUNTIF(FullCompDataSet[ST/Prov],SubjectStateAbbreviation)

B. Return the Key for each record. I had this formula:

{=SMALL(IF(FullCompDataSet[ST/Prov]=SubjectStateAbbreviation,FullCompDataSet[Key]),ROW()-18)}

All of that worked fine, but now instead of simply matching the Subject State to the St/Prov, I have a list of compatible states I need to match (example list at bottom in blue). So, the new output I need is:
A. 6 (because the subject state KY matches records in states of KY, IN, and TN)
B. 413, 420, 434, 418, 404, 410

Can anyone suggest the new formulas I'd need to use for A and B?? I've tried all sorts of combinations of VLOOKUP/INDEX/MATCH, OFFSET, INDIRECT but haven't been able to get it. My apologies if there was a better way to post this issue, I'm still learning to post. THANK YOU!

[TABLE="width: 397"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]SUBJECT STATE[/TD]
[TD]KY[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Key[/TD]
[TD]Address[/TD]
[TD]ST/Prov[/TD]
[/TR]
[TR]
[TD]413[/TD]
[TD]2481 W. Sherman [/TD]
[TD]KY[/TD]
[/TR]
[TR]
[TD]501[/TD]
[TD]1867 Broadway St. [/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]420[/TD]
[TD]563 Allen Rd. [/TD]
[TD]IN[/TD]
[/TR]
[TR]
[TD]422[/TD]
[TD]3677 South Pere Marquette Hwy [/TD]
[TD]MI[/TD]
[/TR]
[TR]
[TD]434[/TD]
[TD]2234 Glover Rd. [/TD]
[TD]KY[/TD]
[/TR]
[TR]
[TD]418[/TD]
[TD]1310 Fleming Ave. [/TD]
[TD]KY[/TD]
[/TR]
[TR]
[TD]431[/TD]
[TD]3801 New Tampa Hwy. [/TD]
[TD]FL[/TD]
[/TR]
[TR]
[TD]404[/TD]
[TD]Clay Ave. [/TD]
[TD]TN[/TD]
[/TR]
[TR]
[TD]401[/TD]
[TD]5450 N. Paramount Blvd. [/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]386[/TD]
[TD]4264 SE. 122nd St. [/TD]
[TD]OR[/TD]
[/TR]
[TR]
[TD]410[/TD]
[TD]3323 NE. 14th Street [/TD]
[TD]TN[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]TOTAL RECORDS IN COMPATIBLE STATES[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]KEY RECORDS[/TD]
[TD="align: right"]413[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]418[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]434[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 186"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]State[/TD]
[TD]Compatible States[/TD]
[/TR]
[TR]
[TD]AL[/TD]
[TD]AL[/TD]
[/TR]
[TR]
[TD]CT[/TD]
[TD]CT[/TD]
[/TR]
[TR]
[TD]CT[/TD]
[TD]ME[/TD]
[/TR]
[TR]
[TD]FL[/TD]
[TD]FL[/TD]
[/TR]
[TR]
[TD]KY[/TD]
[TD]KY[/TD]
[/TR]
[TR]
[TD]KY[/TD]
[TD]IN[/TD]
[/TR]
[TR]
[TD]KY[/TD]
[TD]TN[/TD]
[/TR]
[TR]
[TD]TX[/TD]
[TD]TX[/TD]
[/TR]
</tbody>[/TABLE]

 

[mole999]

if you have a spare column, mayb a bit of vba to run down your main list and for every KY, IN, TN add in the spare column KY, then use that for your reference

 

[merce333]

Thanks for the quick reply, mole!
- Spare columns I have. I'm under a "no VBA" restriction =(
- I believe a complication with that method is the fact that other states, for example, Ohio, may also have a compatible list that include TN, IN, and even KY.
I can envision individual steps on how to solve this, but I don't know how to make the corresponding formulas. Here is how I'm thinking in my head:
1. Do a VLOOKUP on the Subject State / Compatible State list based on the Subject State. This would leave me with this 'filtered' range:
[TABLE="width: 186"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]State[/TD]
[TD]Compatible States[/TD]
[/TR]
[TR]
[TD]KY[/TD]
[TD]KY[/TD]
[/TR]
[TR]
[TD]KY[/TD]
[TD]IN[/TD]
[/TR]
[TR]
[TD]KY[/TD]
[TD]TN[/TD]
[/TR]
</tbody>[/TABLE]

2. Next, do a COUNTIF where Subject State matches any entry in the 2nd column of the Compatible States list.
3. Finally, return all matching Key records where Subject State matches any entry in the 2nd column of the Compatible States list.

 

[merce333]

Mole: I solved this using your suggestion of adding a column - Thank you. I actually added in a concatenated SubjectState&CompState column into the Compatible State List. Then I added in an identical column of concatenated SubjectState&CompState into each row of my FullCompDataSet. That way my old formulas now work the same way because these 2 new columns are a unique match just like the single-entity "ST/Prov" was before.