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"

Bay	Bldg #
204​	1​
115​	3​
338​	6​

 

[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.

Row\Col	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​	1​
27​		115​	3​
28​		338​	6​

 

[Sean15]

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

Sean