Match Top Results

[cjpascoe1]

I'm trying to create a lookup or match, where I would enter a State in a cell and of all the options from the original table, it would bring in the first 10 matches based on the state selected
So this would be similar to a vlookup or match, but would need to pull more than the first results

Sample Results:

State	MO
Property Name	State	Units	Close Date
Property 5	MO	525	3/15/2022
Property 8	MO	331	3/10/2022
Property 17	MO	65	3/9/2022
Property 18	MO	51	3/9/2022
Property 23	MO	98	2/14/2022

SAMPLE DATABASE

Property Name	State	Units	Close Date
Property 1	CO	180	4/7/2022
Property 2	NC	71	3/23/2022
Property 3	CA	81	3/18/2022
Property 4	GA	100	3/16/2022
Property 5	MO	525	3/15/2022
Property 6	OH	240	3/14/2022
Property 7	KS	20	3/11/2022
Property 8	MI	160	3/10/2022
Property 9	MO	331	3/10/2022
Property 10	OK	32	3/10/2022
Property 11	OH	20	3/10/2022
Property 12	MO	65	3/9/2022
Property 13	MO	51	3/9/2022
Property 14	AR	50	3/7/2022
Property 15	TN	90	3/3/2022
Property 16	TN	144	3/3/2022
Property 17	TN	112	3/3/2022
Property 18	TN	100	3/3/2022
Property 19	VA	118	2/24/2022
Property 20	AR	154	2/17/2022
Property 21	MI	175	2/16/2022
Property 22	MO	98	2/14/2022
Property 23	NE	50	2/11/2022
Property 24	NY	56	1/31/2022
Property 25	GA	150	1/31/2022
Property 26	GA	220	1/31/2022
Property 27	OR	35	1/29/2022
Property 28	NY	136	1/27/2022
Property 29	VA	150	1/26/2022
Property 30	PA	344	1/21/2022
Property 31	NY	124	1/14/2022
Property 32	IL	129	1/12/2022

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[cjpascoe1]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Office 365

 

[Fluff]

Thanks for that, please don't forget to update your account details.

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Property Name	State	Units	Close Date			Mo
2	Property 1	CO	180	07/04/2022			Property 5	MO	525	15/03/2022
3	Property 2	NC	71	23/03/2022			Property 9	MO	331	10/03/2022
4	Property 3	CA	81	18/03/2022			Property 12	MO	65	09/03/2022
5	Property 4	GA	100	16/03/2022			Property 13	MO	51	09/03/2022
6	Property 5	MO	525	15/03/2022			Property 22	MO	98	14/02/2022
7	Property 6	OH	240	14/03/2022
8	Property 7	KS	20	11/03/2022
9	Property 8	MI	160	10/03/2022
10	Property 9	MO	331	10/03/2022
11	Property 10	OK	32	10/03/2022
12	Property 11	OH	20	10/03/2022
13	Property 12	MO	65	09/03/2022
14	Property 13	MO	51	09/03/2022
15	Property 14	AR	50	07/03/2022
16	Property 15	TN	90	03/03/2022
17	Property 16	TN	144	03/03/2022
18	Property 17	TN	112	03/03/2022
19	Property 18	TN	100	03/03/2022
20	Property 19	VA	118	24/02/2022
21	Property 20	AR	154	17/02/2022
22	Property 21	MI	175	16/02/2022
23	Property 22	MO	98	14/02/2022
24	Property 23	NE	50	11/02/2022
25	Property 24	NY	56	31/01/2022
26	Property 25	GA	150	31/01/2022
27	Property 26	GA	220	31/01/2022
28	Property 27	OR	35	29/01/2022
29	Property 28	NY	136	27/01/2022
30	Property 29	VA	150	26/01/2022
31	Property 30	PA	344	21/01/2022
32	Property 31	NY	124	14/01/2022
33	Property 32	IL	129	12/01/2022
34
Main

Cell Formulas
Range	Formula
G2:J6	G2	=LET(f,FILTER(A2:D300,B2:B300=G1),INDEX(f,SEQUENCE(MIN(10,ROWS(f))),{1,2,3,4}))
Dynamic array formulas.