Help with INDEX and MATCH

[Sean15]

Hi:

Please see below.

Excel 2010 is returning #NA for lookup values.

The required value for 204 is 1, 115 is 3, and 6.

Could you advise please?

Excel 2010
	E	F	G	H	I	J	K	L	M
5	Bldg #	Bay	Bay	Bay	Bay	Bay	Bay	Bay	Bay
6	1	101	102	103	104	105	106
7	1	201	202	203	204	205	206
8	1	301	302	303	304	305	306
9	2	207	208	209	210	211	212	213	214
10	2	107	108	109	110	111	112	113	114
11	3	115	116	117	118	119
12	3	215	216	217	218	219
13	3	315	316	317	318	319
14	4	121	122	123	124	125	126
15	4	221	222	223	224	225	226
16	4	321	322	323	324	325	326
17	5	127	128	129	130	131	132
18	5	227	228	229	230	231	232
19	5	327	328	329	330	331	332
20	6	133	134	135	136	137	138
21	6	233	234	235	236	237	238
22	6	333	334	335	336	337	338
23
24
25		Bay	Bldg
26		204	#N/A
27		115	#N/A
28		338	#N/A
Sheet1 (2)

Cell Formulas
Range	Formula
G26	=INDEX($E$6:$E$22,MATCH(F26,$F$6:$N$22,0))
G27	=INDEX($E$6:$E$22,MATCH(F27,$F$6:$N$22,0))
G28	=INDEX($E$6:$E$22,MATCH(F28,$F$6:$N$22,0))

Thanks,

Sean

 

[shift-del]

Sean,

MATCH() only works on one row or one column but not on a range.

=AGGREGATE(15,6,$E$6:$E$22/($F$6:$M$22=F26),1)

 

[sandy666]

or use PowerQuery

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Bldg #", Int64.Type}, {"Bay", Int64.Type}, {"Bay2", Int64.Type}, {"Bay3", Int64.Type}, {"Bay4", Int64.Type}, {"Bay5", Int64.Type}, {"Bay6", Int64.Type}, {"Bay7", Int64.Type}, {"Bay8", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Bldg #"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"})
in
    #"Removed Columns"

let
    Source = Table.NestedJoin(Table2,{"Bay"},Table1,{"Value"},"Table1",JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(Source, "Table1", {"Bldg #"}, {"Bldg #"})
in
    #"Expanded Table1"

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Bay[/td][td=bgcolor:#70AD47]Bldg #[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

204​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

115​

[/td][td]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

338​

[/td][td=bgcolor:#E2EFDA]

6​

[/td][/tr]
[/table]

 

[strooman]

In G26

=INDEX($E$6:$E$22;SUMPRODUCT(--($F$6:$M$22=F26)*ROW($F$6:$M$22))-ROW($F$6:$M$22)+1)

and copy down.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][td]

M​

[/td][/tr][tr][td]

5​

[/td][td="bgcolor:#0070C0"]Bldg #[/td][td="bgcolor:#0070C0"]Bay[/td][td="bgcolor:#0070C0"]Bay[/td][td="bgcolor:#0070C0"]Bay[/td][td="bgcolor:#0070C0"]Bay[/td][td="bgcolor:#0070C0"]Bay[/td][td="bgcolor:#0070C0"]Bay[/td][td="bgcolor:#0070C0"]Bay[/td][td="bgcolor:#0070C0"]Bay[/td][/tr]
[tr][td]

6​

[/td][td]

1​

[/td][td]

101​

[/td][td]

102​

[/td][td]

103​

[/td][td]

104​

[/td][td]

105​

[/td][td]

106​

[/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td]

1​

[/td][td]

201​

[/td][td]

202​

[/td][td]

203​

[/td][td="bgcolor:#FFD9D9"]

204​

[/td][td]

205​

[/td][td]

206​

[/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td]

1​

[/td][td]

301​

[/td][td]

302​

[/td][td]

303​

[/td][td]

304​

[/td][td]

305​

[/td][td]

306​

[/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td]

2​

[/td][td]

207​

[/td][td]

208​

[/td][td]

209​

[/td][td]

210​

[/td][td]

211​

[/td][td]

212​

[/td][td]

213​

[/td][td]

214​

[/td][/tr]
[tr][td]

10​

[/td][td]

2​

[/td][td]

107​

[/td][td]

108​

[/td][td]

109​

[/td][td]

110​

[/td][td]

111​

[/td][td]

112​

[/td][td]

113​

[/td][td]

114​

[/td][/tr]
[tr][td]

11​

[/td][td]

3​

[/td][td="bgcolor:#FFD9D9"]

115​

[/td][td]

116​

[/td][td]

117​

[/td][td]

118​

[/td][td]

119​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

12​

[/td][td]

3​

[/td][td]

215​

[/td][td]

216​

[/td][td]

217​

[/td][td]

218​

[/td][td]

219​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

13​

[/td][td]

3​

[/td][td]

315​

[/td][td]

316​

[/td][td]

317​

[/td][td]

318​

[/td][td]

319​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

14​

[/td][td]

4​

[/td][td]

121​

[/td][td]

122​

[/td][td]

123​

[/td][td]

124​

[/td][td]

125​

[/td][td]

126​

[/td][td][/td][td][/td][/tr]
[tr][td]

15​

[/td][td]

4​

[/td][td]

221​

[/td][td]

222​

[/td][td]

223​

[/td][td]

224​

[/td][td]

225​

[/td][td]

226​

[/td][td][/td][td][/td][/tr]
[tr][td]

16​

[/td][td]

4​

[/td][td]

321​

[/td][td]

322​

[/td][td]

323​

[/td][td]

324​

[/td][td]

325​

[/td][td]

326​

[/td][td][/td][td][/td][/tr]
[tr][td]

17​

[/td][td]

5​

[/td][td]

127​

[/td][td]

128​

[/td][td]

129​

[/td][td]

130​

[/td][td]

131​

[/td][td]

132​

[/td][td][/td][td][/td][/tr]
[tr][td]

18​

[/td][td]

5​

[/td][td]

227​

[/td][td]

228​

[/td][td]

229​

[/td][td]

230​

[/td][td]

231​

[/td][td]

232​

[/td][td][/td][td][/td][/tr]
[tr][td]

19​

[/td][td]

5​

[/td][td]

327​

[/td][td]

328​

[/td][td]

329​

[/td][td]

330​

[/td][td]

331​

[/td][td]

332​

[/td][td][/td][td][/td][/tr]
[tr][td]

20​

[/td][td]

6​

[/td][td]

133​

[/td][td]

134​

[/td][td]

135​

[/td][td]

136​

[/td][td]

137​

[/td][td]

138​

[/td][td][/td][td][/td][/tr]
[tr][td]

21​

[/td][td]

6​

[/td][td]

233​

[/td][td]

234​

[/td][td]

235​

[/td][td]

236​

[/td][td]

237​

[/td][td]

238​

[/td][td][/td][td][/td][/tr]
[tr][td]

22​

[/td][td]

6​

[/td][td]

333​

[/td][td]

334​

[/td][td]

335​

[/td][td]

336​

[/td][td]

337​

[/td][td="bgcolor:#FFD9D9"]

338​

[/td][td][/td][td][/td][/tr]
[tr][td]

23​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

24​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

25​

[/td][td][/td][td="bgcolor:#0070C0"]Bay[/td][td="bgcolor:#0070C0"]Bldg#[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

26​

[/td][td][/td][td="bgcolor:#FFD9D9"]

204​

[/td][td="bgcolor:#FFFF00"]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

27​

[/td][td][/td][td="bgcolor:#FFD9D9"]

115​

[/td][td="bgcolor:#FFFF00"]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

28​

[/td][td][/td][td="bgcolor:#FFD9D9"]

338​

[/td][td="bgcolor:#FFFF00"]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[Sean15]

@shift-del, strooman - thank you very much.
@sandy666 - I have never used PowerQuery but thanks for your insight.

Sean