Array Value in INDEX MATCH functions

[rclackjr]

Everyone,

I'm not sure if it is even possible but I figured if it was, someone on here would know how to do it. I have a spreadsheet with every county in every state in the US and their max rents determined by HUD. I have a matrix built that shows the max rent based upon bedrooms (Unit Type) and Percent of the Area Median Income (AMI). Essentially I am trying to get the array portion of both the INDEX and MATCH functions in my spreadsheet to change based on the value in another cell so that as the user of the spreadsheet has projects in different states and counties he/she can just chose the state and county from the data validation boxes and the matrix will update automatically. I would post my excel file as well but as this is my first post I have no idea how, I have attached a table and will post the formulas and a better explanation of the goal below.

B

C

D

E

F

G

H

I

J

K

L

M

2

Autauga

30%

40%

50%

60%

State

AL

3

STD

a

b

c

d

County

Autauga

4

1BD

e

f

g

h

AutaugaALRents

AutaugaALUnitType

AutaugaALAMI

5

2BD

i

j

k

l

6

3BD

m

n

o

p

30%

40%

50%

60%

7

4BD

q

r

s

t

STD

a

b

c

d

8

5BD

u

v

w

x

1BD

e

f

g

h

9

Baldwin

30%

40%

50%

60%

2BD

i

j

k

l

10

STD

y

z

aa

bb

3BD

m

n

o

p

11

1BD

cc

dd

ee

ff

4BD

q

r

s

t

12

2BD

gg

hh

ii

jj

5BD

u

v

w

x

13

3BD

kk

ll

mm

nn

14

4BD

oo

pp

qq

rr

15

5BD

ss

tt

uu

vv

<tbody>
</tbody>

D2:G2 is named "AutaugaALAMI", C3:C8 is named "AutaugaALUnitType", D3:G8 is named "AutaugaALRents". The arrays for Baldwin are the same just with the corresponding county name instead of "Autauga".

Cells J2 and J3 are data validation lists pulling from another hidden page with all of the states and counties listed.

I4 = J3&J2&"Rents", J4 = J3&J2&"UnitType", K4 = J3&J2&"AMI", and they change as the user changes J2 and J3 to his/her specific state/county

J7 = INDEX(AutaugaALRents,MATCH($I7,AutaugaALUnitType,0),MATCH(J$6,AutaugaALAMI,0)) and the formula changes depending upon which Unit Type or AMI the user is attempting to get the information for.

My goal is change the array lookup portions of the INDEX and MATCH functions to equal the arrays specific to whatever county and state the user has. I'm hoping those arrays can pull off of the information in cells I4:K4 which will be hidden instead of the user having to input the specific array names in the formula every time.

 

[Leo Skywalker]

Hello,

If you can use column A to type the State next to the county that is in column B, (B2: Autauga, then A2: AL; B8: Baldwin, then A9: AL, let's say B1500: Chicago, then A1500: IL), then the solution would not even need having three named ranges for every county.

P1:

{=MATCH(J2&J3,$A:$A&$B:$B,0)-6}

<tbody>
</tbody>

This is an array formula, entered with Control+Shift+Enter

You could hide column P, in order not to see the working number. Also, $A:$A and $B:$B may be reduced to $A$1:$A$30000 and $B$1:$B$30000, as there are less than 3200 counties and each county uses less than 10 rows.

J7:

=INDEX($D:$G,ROW()+$P$1,COLUMN(D1)-3)

Here, too, $D:$G can be reduced to $D$1:$G$30000

Copy across and down.


Hope this works.

 

[rclackjr]

That worked perfectly! It looks like it was a classic case of over-analysis and complication on my part- Thanks!

 

[Leo Skywalker]

Glad to help!!