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!

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

<colgroup><col><col><col></colgroup><tbody>
</tbody>

State	Compatible States
AL	AL
CT	CT
CT	ME
FL	FL
KY	KY
KY	IN
KY	TN
TX	TX

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[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:

State	Compatible States
KY	KY
KY	IN
KY	TN

<colgroup><col><col></colgroup><tbody>
</tbody>

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.