Formula to look up a cell - based on values from two different cells

[PCloadletter]

Hello all,

Been beating myself over the head on how to word this. Thanks in advance for any help.

I currently have two columns - one showing an employees home city and the other their work city and want to pull data from a table to fill in the third column.

UPDATED HOME CITY	EMPL_WORK_CITY	Qualified Zones
Vancouver	Vancouver
Vancouver	North Vancouver
North Vancouver	North Vancouver

The source of the table looks like this :

	Burnaby	Coquitlam	Delta	Langley	Maple Ridge	New Westminster	North Vancouver	Pitt Meadows	Port Coquitlam	Port Moody	Richmond	Surrey	Tsawwassen	Vancouver	West Vancouver	White Rock
Burnaby	1	2	2	2	2	1	2	2	2	2	2	2	2	2	2	2
Coquitlam	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
Delta	2	2	1	1	2	2	3	2	2	2	1	1	1	3	3	1
Langley	2	2	1	1	2	2	3	2	2	2	3	1	1	3	3	1
Maple Ridge	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
New Westminster	1	2	2	2	2	1	2	2	2	2	2	2	2	2	2	2
North Vancouver	2	3	3	3	3	2	1	3	3	3	2	3	3	2	1	3
Pitt Meadows	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
Port Coquitlam	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
Port Moody	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
Richmond	2	3	1	3	3	2	2	3	3	3	1	3	1	2	2	3
Surrey	2	2	1	1	2	2	3	2	2	2	3	1	1	3	3	1
Tsawwassen	2	2	1	1	2	2	3	2	2	2	1	1	1	3	3	1
Vancouver	2	3	3	3	3	2	2	3	3	3	2	3	3	1	2	3
West Vancouver	2	3	3	3	3	2	1	3	3	3	2	3	3	2	1	3
White Rock	2	2	1	1	2	2	3	2	2	2	3	1	1	3	3	1

Looking to pull the number from the chart into the "Qualified Zones" column.

Thank you again in advance.

 

[alansidman]

Book8
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Burnaby	Coquitlam	Delta	Langley	Maple Ridge	New Westminster	North Vancouver	Pitt Meadows	Port Coquitlam	Port Moody	Richmond	Surrey	Tsawwassen	Vancouver	West Vancouver	White Rock
2	Burnaby	1	2	2	2	2	1	2	2	2	2	2	2	2	2	2	2
3	Coquitlam	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
4	Delta	2	2	1	1	2	2	3	2	2	2	1	1	1	3	3	1
5	Langley	2	2	1	1	2	2	3	2	2	2	3	1	1	3	3	1
6	Maple Ridge	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
7	New Westminster	1	2	2	2	2	1	2	2	2	2	2	2	2	2	2	2
8	North Vancouver	2	3	3	3	3	2	1	3	3	3	2	3	3	2	1	3
9	Pitt Meadows	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
10	Port Coquitlam	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
11	Port Moody	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
12	Richmond	2	3	1	3	3	2	2	3	3	3	1	3	1	2	2	3
13	Surrey	2	2	1	1	2	2	3	2	2	2	3	1	1	3	3	1
14	Tsawwassen	2	2	1	1	2	2	3	2	2	2	1	1	1	3	3	1
15	Vancouver	2	3	3	3	3	2	2	3	3	3	2	3	3	1	2	3
16	West Vancouver	2	3	3	3	3	2	1	3	3	3	2	3	3	2	1	3
17	White Rock	2	2	1	1	2	2	3	2	2	2	3	1	1	3	3	1
18
19
20	UPDATED HOME CITY	EMPL_WORK_CITY	Qualified Zones
21	Vancouver	Vancouver	1
22	Vancouver	North Vancouver	2
23	North Vancouver	North Vancouver	1
Sheet2

Cell Formulas
Range	Formula
C21:C23	C21	=INDEX($A$1:$Q$17,MATCH(A21,$A$2:$A$17,0),MATCH(B21,$B$1:$Q$1,0))

 

[Peter_SSs]

If you are using your 365 version & it is of interest to you, Alan's suggestion can be modified to return all the results from a single formula without the need to copy it down (& then none of the '$' anchors would be required either)

25 03 06.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Burnaby	Coquitlam	Delta	Langley	Maple Ridge	New Westminster	North Vancouver	Pitt Meadows	Port Coquitlam	Port Moody	Richmond	Surrey	Tsawwassen	Vancouver	West Vancouver	White Rock
2	Burnaby	1	2	2	2	2	1	2	2	2	2	2	2	2	2	2	2
3	Coquitlam	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
4	Delta	2	2	1	1	2	2	3	2	2	2	1	1	1	3	3	1
5	Langley	2	2	1	1	2	2	3	2	2	2	3	1	1	3	3	1
6	Maple Ridge	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
7	New Westminster	1	2	2	2	2	1	2	2	2	2	2	2	2	2	2	2
8	North Vancouver	2	3	3	3	3	2	1	3	3	3	2	3	3	2	1	3
9	Pitt Meadows	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
10	Port Coquitlam	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
11	Port Moody	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
12	Richmond	2	3	1	3	3	2	2	3	3	3	1	3	1	2	2	3
13	Surrey	2	2	1	1	2	2	3	2	2	2	3	1	1	3	3	1
14	Tsawwassen	2	2	1	1	2	2	3	2	2	2	1	1	1	3	3	1
15	Vancouver	2	3	3	3	3	2	2	3	3	3	2	3	3	1	2	3
16	West Vancouver	2	3	3	3	3	2	1	3	3	3	2	3	3	2	1	3
17	White Rock	2	2	1	1	2	2	3	2	2	2	3	1	1	3	3	1
18
19
20	UPDATED HOME CITY	EMPL_WORK_CITY	Qualified Zones
21	Vancouver	Vancouver	1
22	Vancouver	North Vancouver	2
23	North Vancouver	North Vancouver	1
Zones

Cell Formulas
Range	Formula
C21:C23	C21	=INDEX(A1:Q17,MATCH(A21:A23,A2:A17,0),MATCH(B21:B23,B1:Q1,0))
Dynamic array formulas.

 

[PCloadletter]

Book8
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Burnaby	Coquitlam	Delta	Langley	Maple Ridge	New Westminster	North Vancouver	Pitt Meadows	Port Coquitlam	Port Moody	Richmond	Surrey	Tsawwassen	Vancouver	West Vancouver	White Rock
2	Burnaby	1	2	2	2	2	1	2	2	2	2	2	2	2	2	2	2
3	Coquitlam	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
4	Delta	2	2	1	1	2	2	3	2	2	2	1	1	1	3	3	1
5	Langley	2	2	1	1	2	2	3	2	2	2	3	1	1	3	3	1
6	Maple Ridge	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
7	New Westminster	1	2	2	2	2	1	2	2	2	2	2	2	2	2	2	2
8	North Vancouver	2	3	3	3	3	2	1	3	3	3	2	3	3	2	1	3
9	Pitt Meadows	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
10	Port Coquitlam	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
11	Port Moody	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
12	Richmond	2	3	1	3	3	2	2	3	3	3	1	3	1	2	2	3
13	Surrey	2	2	1	1	2	2	3	2	2	2	3	1	1	3	3	1
14	Tsawwassen	2	2	1	1	2	2	3	2	2	2	1	1	1	3	3	1
15	Vancouver	2	3	3	3	3	2	2	3	3	3	2	3	3	1	2	3
16	West Vancouver	2	3	3	3	3	2	1	3	3	3	2	3	3	2	1	3
17	White Rock	2	2	1	1	2	2	3	2	2	2	3	1	1	3	3	1
18
19
20	UPDATED HOME CITY	EMPL_WORK_CITY	Qualified Zones
21	Vancouver	Vancouver	1
22	Vancouver	North Vancouver	2
23	North Vancouver	North Vancouver	1
Sheet2

Cell Formulas
Range	Formula
C21:C23	C21	=INDEX($A$1:$Q$17,MATCH(A21,$A$2:$A$17,0),MATCH(B21,$B$1:$Q$1,0))

Thank you!

I coped the formula and everything seems to work except for when I try to enter Burnaby it returns the city listed beside it.

	Burnaby	Coquitlam	Delta	Langley	Maple Ridge	New Westminster	North Vancouver	Pitt Meadows	Port Coquitlam	Port Moody	Richmond	Surrey	Tsawwassen	Vancouver	West Vancouver	White Rock
Burnaby	1	2	2	2	2	1	2	2	2	2	2	2	2	2	2	2
Coquitlam	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
Delta	2	2	1	1	2	2	3	2	2	2	1	1	1	3	3	1
Langley	2	2	1	1	2	2	3	2	2	2	3	1	1	3	3	1
Maple Ridge	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
New Westminster	1	2	2	2	2	1	2	2	2	2	2	2	2	2	2	2
North Vancouver	2	3	3	3	3	2	1	3	3	3	2	3	3	2	1	3
Pitt Meadows	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
Port Coquitlam	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
Port Moody	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
Richmond	2	3	1	3	3	2	2	3	3	3	1	3	1	2	2	3
Surrey	2	2	1	1	2	2	3	2	2	2	3	1	1	3	3	1
Tsawwassen	2	2	1	1	2	2	3	2	2	2	1	1	1	3	3	1
Vancouver	2	3	3	3	3	2	2	3	3	3	2	3	3	1	2	3
West Vancouver	2	3	3	3	3	2	1	3	3	3	2	3	3	2	1	3
White Rock	2	2	1	1	2	2	3	2	2	2	3	1	1	3	3	1
UPDATED HOME CITY	EMPL_WORK_CITY	Qualifed Zones
Burnaby	Coquitlam	Burnaby
Burnaby	Delta	Coquitlam
Burnaby	Langley	Delta
Burnaby	Maple Ridge	Langley
Burnaby	New Westminster	Maple Ridge
Burnaby	North Vancouver	New Westminster
Burnaby	Pitt Meadows	North Vancouver
Burnaby	Port Coquitlam	Pitt Meadows
Burnaby	Port Moody	Port Coquitlam
Burnaby	Richmond	Port Moody
Burnaby	Surrey	Richmond
Burnaby	Tsawwassen	Surrey
Burnaby	Vancouver	Tsawwassen
Burnaby	West Vancouver	Vancouver
Burnaby	White Rock	West Vancouver
Delta	Coquitlam	2
Delta	Delta	1
Delta	Burnaby	Coquitlam
Delta	Coquitlam	2
Delta	Delta	1
Delta	Langley	2
Delta	Maple Ridge	2
Delta	New Westminster	1
Delta	North Vancouver	2
Delta	Pitt Meadows	3
Delta	Port Coquitlam	1
Delta	Port Moody	1
Delta	Richmond	1
Delta	Surrey	3
Delta	Tsawwassen	2
Delta	Vancouver	2
Delta	West Vancouver	3
Delta	White Rock	3
Langley	Coquitlam	2
Langley	Delta	2
Langley	Burnaby	Delta
Langley	Coquitlam	2
Langley	Delta	2
Langley	Langley	1
Langley	Maple Ridge	1
Langley	New Westminster	2
Langley	North Vancouver	2
Langley	Pitt Meadows	3
Langley	Port Coquitlam	2
Langley	Port Moody	2
Langley	Richmond	2
Langley	Surrey	1
Langley	Tsawwassen	1
Langley	Vancouver	1
Langley	West Vancouver	3
Langley	White Rock	3
Langley	Delta	2
White Rock	New Westminster	3
White Rock	North Vancouver	2
White Rock	Pitt Meadows	1
White Rock	Port Coquitlam	3
White Rock	Port Moody	3
White Rock	Richmond	3
White Rock	Surrey	2
White Rock	Tsawwassen	3
White Rock	Vancouver	3
White Rock	West Vancouver	2
White Rock	White Rock	1

 

[PCloadletter]

If you are using your 365 version & it is of interest to you, Alan's suggestion can be modified to return all the results from a single formula without the need to copy it down (& then none of the '$' anchors would be required either)

25 03 06.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Burnaby	Coquitlam	Delta	Langley	Maple Ridge	New Westminster	North Vancouver	Pitt Meadows	Port Coquitlam	Port Moody	Richmond	Surrey	Tsawwassen	Vancouver	West Vancouver	White Rock
2	Burnaby	1	2	2	2	2	1	2	2	2	2	2	2	2	2	2	2
3	Coquitlam	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
4	Delta	2	2	1	1	2	2	3	2	2	2	1	1	1	3	3	1
5	Langley	2	2	1	1	2	2	3	2	2	2	3	1	1	3	3	1
6	Maple Ridge	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
7	New Westminster	1	2	2	2	2	1	2	2	2	2	2	2	2	2	2	2
8	North Vancouver	2	3	3	3	3	2	1	3	3	3	2	3	3	2	1	3
9	Pitt Meadows	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
10	Port Coquitlam	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
11	Port Moody	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
12	Richmond	2	3	1	3	3	2	2	3	3	3	1	3	1	2	2	3
13	Surrey	2	2	1	1	2	2	3	2	2	2	3	1	1	3	3	1
14	Tsawwassen	2	2	1	1	2	2	3	2	2	2	1	1	1	3	3	1
15	Vancouver	2	3	3	3	3	2	2	3	3	3	2	3	3	1	2	3
16	West Vancouver	2	3	3	3	3	2	1	3	3	3	2	3	3	2	1	3
17	White Rock	2	2	1	1	2	2	3	2	2	2	3	1	1	3	3	1
18
19
20	UPDATED HOME CITY	EMPL_WORK_CITY	Qualified Zones
21	Vancouver	Vancouver	1
22	Vancouver	North Vancouver	2
23	North Vancouver	North Vancouver	1
Zones

Cell Formulas
Range	Formula
C21:C23	C21	=INDEX(A1:Q17,MATCH(A21:A23,A2:A17,0),MATCH(B21:B23,B1:Q1,0))
Dynamic array formulas.

Thank you, unfortunately my version doesn't seem to support this.

 

[Peter_SSs]

unfortunately my version doesn't seem to support this.

Cheers. Sounds like you are not using Microsoft 365 then, so my 'If ..' preface did not apply.

If you are using your 365 version ..

 

[Peter_SSs]

except for when I try to enter Burnaby it returns the city listed beside it.

Yes, there are a couple of small glitches in the formula references. Try this correction to the original formula - or the VLOOKUP alternative provided beside.

25 03 06.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Burnaby	Coquitlam	Delta	Langley	Maple Ridge	New Westminster	North Vancouver	Pitt Meadows	Port Coquitlam	Port Moody	Richmond	Surrey	Tsawwassen	Vancouver	West Vancouver	White Rock
2	Burnaby	1	2	2	2	2	1	2	2	2	2	2	2	2	2	2	2
3	Coquitlam	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
4	Delta	2	2	1	1	2	2	3	2	2	2	1	1	1	3	3	1
5	Langley	2	2	1	1	2	2	3	2	2	2	3	1	1	3	3	1
6	Maple Ridge	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
7	New Westminster	1	2	2	2	2	1	2	2	2	2	2	2	2	2	2	2
8	North Vancouver	2	3	3	3	3	2	1	3	3	3	2	3	3	2	1	3
9	Pitt Meadows	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
10	Port Coquitlam	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
11	Port Moody	2	1	2	2	1	2	3	1	1	1	3	2	2	3	3	2
12	Richmond	2	3	1	3	3	2	2	3	3	3	1	3	1	2	2	3
13	Surrey	2	2	1	1	2	2	3	2	2	2	3	1	1	3	3	1
14	Tsawwassen	2	2	1	1	2	2	3	2	2	2	1	1	1	3	3	1
15	Vancouver	2	3	3	3	3	2	2	3	3	3	2	3	3	1	2	3
16	West Vancouver	2	3	3	3	3	2	1	3	3	3	2	3	3	2	1	3
17	White Rock	2	2	1	1	2	2	3	2	2	2	3	1	1	3	3	1
18
19
20	UPDATED HOME CITY	EMPL_WORK_CITY	Qualified Zones
21	Burnaby	Coquitlam	2	2
22	Burnaby	Delta	2	2
23	Burnaby	Langley	2	2
24	Burnaby	Maple Ridge	2	2
25	Burnaby	New Westminster	1	1
26	Burnaby	North Vancouver	2	2
27	Burnaby	Pitt Meadows	2	2
28	Burnaby	Port Coquitlam	2	2
29	Burnaby	Port Moody	2	2
30	Burnaby	Richmond	2	2
31	Burnaby	Surrey	2	2
32	Burnaby	Tsawwassen	2	2
33	Burnaby	Vancouver	2	2
34	Burnaby	West Vancouver	2	2
35	Burnaby	White Rock	2	2
36	Delta	Coquitlam	2	2
37	Delta	Delta	1	1
38	Delta	Burnaby	2	2
39	Delta	Coquitlam	2	2
40	Delta	Delta	1	1
41	Delta	Langley	1	1
42	Delta	Maple Ridge	2	2
43	Delta	New Westminster	2	2
44	Delta	North Vancouver	3	3
45	Delta	Pitt Meadows	2	2
46	Delta	Port Coquitlam	2	2
47	Delta	Port Moody	2	2
48	Delta	Richmond	1	1
49	Delta	Surrey	1	1
50	Delta	Tsawwassen	1	1
51	Delta	Vancouver	3	3
52	Delta	West Vancouver	3	3
53	Delta	White Rock	1	1
54	Langley	Coquitlam	2	2
55	Langley	Delta	1	1
56	Langley	Burnaby	2	2
57	Langley	Coquitlam	2	2
58	Langley	Delta	1	1
59	Langley	Langley	1	1
60	Langley	Maple Ridge	2	2
61	Langley	New Westminster	2	2
62	Langley	North Vancouver	3	3
63	Langley	Pitt Meadows	2	2
64	Langley	Port Coquitlam	2	2
65	Langley	Port Moody	2	2
66	Langley	Richmond	3	3
67	Langley	Surrey	1	1
68	Langley	Tsawwassen	1	1
69	Langley	Vancouver	3	3
70	Langley	West Vancouver	3	3
71	Langley	White Rock	1	1
72	Langley	Delta	1	1
73	White Rock	New Westminster	2	2
74	White Rock	North Vancouver	3	3
75	White Rock	Pitt Meadows	2	2
76	White Rock	Port Coquitlam	2	2
77	White Rock	Port Moody	2	2
78	White Rock	Richmond	3	3
79	White Rock	Surrey	1	1
80	White Rock	Tsawwassen	1	1
81	White Rock	Vancouver	3	3
82	White Rock	West Vancouver	3	3
83	White Rock	White Rock	1	1
Zones

Cell Formulas
Range	Formula
C21:C83	C21	=INDEX(B$2:Q$17,MATCH(A21,A$2:A$17,0),MATCH(B21,B$1:Q$1,0))
D21:D83	D21	=VLOOKUP(A21,A$2:Q$17,MATCH(B21,A$1:Q$1,0),0)