List of values based on matching criteria in another column

[TheMacroNoob]

Hello excel experts,

I want to grab a list of unit numbers based on criteria in another column.

If a row in column G contains "Tax Credit", return the corresponding row value in column B.
There is a formula that accomplishes this task: =INDEX(Sheet1!$B$8:$B$86,SMALL(IF("Tax Credit"=Sheet1!$G$8:$G$86,ROW(Sheet1!$G$8:$G$86)-ROW(Sheet1!$G$8)+1),ROW(1:1)))

Is this really the only solution? Can you use something like an XLOOKUP or VLOOKUP with some way to give the next matching value?

Copy of Lonnie Adkins 980 Landlord MNOI Application.xlsx
	A	B	C	D	E	F	G
6		Unit					Program
7	Current/Notice Residents
8		32-176					Sec 8
9		50-179					Sec 8
10		43-139					Sec 8
11		27-389					Sec 8
12		13-395					Sec 8
13		31-364					Sec 8
14		10-332					Sec 8
15		24-348					Sec 8
16		13-420					Sec 8
17		26-348					Sec 8
18		30-129					Sec 8
19		46-223					Sec 8
20		34-445					Sec 8
21		37-314					Sec 8
22		28-421					Sec 8
23		18-361					Sec 8
24		22-341					Tax Credit
25		17-432					Sec 8
26		46-156					Sec 8
27		15-155					Tax Credit
28		16-287					Sec 8
29		11-138					Sec 8
30		21-415					Tax Credit
31		43-194					Sec 8
32		38-256					Sec 8
33		11-430					Sec 8
34		42-382					Sec 8
35		21-372					Sec 8
36		45-170					Sec 8
37		47-334
38		16-346					Sec 8
39		17-166					Tax Credit
40		41-467					Sec 8
41		30-216					Tax Credit
42		22-117					Sec 8
43		11-242					Tax Credit
44		30-483					Sec 8
45		29-490					Sec 8
46		45-397					None
47		40-300					Tax Credit
48		14-334					Tax Credit
49		38-349					Tax Credit
50		45-407					Tax Credit
51		50-113					Tax Credit
52		42-389					Tax Credit
53		33-413					Sec 8
54		40-378					Tax Credit
55		25-109					Tax Credit
56		28-116					Sec 8
57		48-281					Sec 8
58		28-352					Sec 8
59		14-352					Tax Credit
60		22-252					Tax Credit
61		26-432					Sec 8
62		27-294					Tax Credit
63		12-167					Tax Credit
64		27-297					Sec 8
65		18-451					Tax Credit
66		20-397					Sec 8
67		17-333					Sec 8
68		47-384					Tax Credit
69		29-350					Sec 8
70		26-194					Sec 8
71		18-178					Sec 8
72		17-387					Tax Credit
73		33-123					Sec 8
74		24-183					Sec 8
75		44-471					Tax Credit
76		36-192					Tax Credit
77		43-318					Sec 8
78		45-251					Sec 8
79		19-365					Sec 8
80		48-492					Sec 8
81		44-327					Tax Credit
82		50-150					Tax Credit
83		41-440					Tax Credit
84		42-360					Tax Credit
85		43-290					Sec 8
86		50-486					Tax Credit
Sheet1

 

[DRSteele]

If I understand correctly about what you're after, try this: =FILTER(B8:B86,G8:G86="Tax Credit")

 

[TheMacroNoob]

If I understand correctly about what you're after, try this: =FILTER(B8:B86,G8:G86="Tax Credit")

That works pretty well to me. Thank you DRSteele!

 

[DRSteele]

You're welcome. Glad it works.

You might benefit from watching some videos on youtube. The channel is awesome. Learn lots about filter, here.