Index & Match Question

[chingching831]

Hi team,

The following table is the data source I have got

Names & ID	Jan-23	Feb-23	Mar-23	Apr-23
Janice_TY1	200	230	300	400
Mavis_TY1	156	210	235	983
Janice_TY2	436	294	456	201
Mavis_TY2	176	267	688	469

How can I get the numbers from the upper table to match the first two columns ? I think Index & Match can help.. but I don't know how.

Jan-23	Janice_TY1	?
Apr-23	Mavis_TY2	?
Feb-23	Mavis_TY1	?
Mar-23	Mavis_TY2	?

Thank you.

Cheers, Sam

 

[Georgiboy]

Maybe like this:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Names & ID	Jan-23	Feb-23	Mar-23	Apr-23
2	Janice_TY1	200	230	300	400			Jan-23	Janice_TY1	200
3	Mavis_TY1	156	210	235	983			Apr-23	Mavis_TY2	469
4	Janice_TY2	436	294	456	201			Feb-23	Mavis_TY1	210
5	Mavis_TY2	176	267	688	469			Mar-23	Mavis_TY2	688
Sheet1

Cell Formulas
Range	Formula
J2:J5	J2	=INDEX($B$2:$E$5,MATCH(I2,$A$2:$A$5,0),MATCH(H2,$B$1:$E$1,0))

 

[etaf]

=INDEX($B$2:$E$5,MATCH(B10,$A$2:$A$5,0),MATCH(A10,$B$1:$E$1,0))

Two-Way Lookups in Excel

Book9
	A	B	C	D	E	F
1	Names & ID	Jan-23	Feb-23	Mar-23	Apr-23
2	Janice_TY1	200	230	300	400
3	Mavis_TY1	156	210	235	983
4	Janice_TY2	436	294	456	201
5	Mavis_TY2	176	267	688	469
6
7
8
9
10	Jan-23	Janice_TY1	200
11	Apr-23	Mavis_TY2	469
12	Feb-23	Mavis_TY1	210
13	Mar-23	Mavis_TY2	688
Sheet1

Cell Formulas
Range	Formula
C10:C13	C10	=INDEX($B$2:$E$5,MATCH(B10,$A$2:$A$5,0),MATCH(A10,$B$1:$E$1,0))

you can pu tinto a seperate sheet as required

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E
1	Names & ID	Jan-23	Feb-23	Mar-23	Apr-23
2	Janice_TY1	200	230	300	400
3	Mavis_TY1	156	210	235	983
4	Janice_TY2	436	294	456	201
5	Mavis_TY2	176	267	688	469
6
7
8
9
10	Jan-23	Janice_TY1	200
11	Apr-23	Mavis_TY2	469
12	Feb-23	Mavis_TY1	210
13	Mar-23	Mavis_TY2	688
14
Master

Cell Formulas
Range	Formula
C10:C13	C10	=INDEX($B$2:$E$5,MATCH(B10,$A$2:$A$5,0),MATCH(A10,$B$1:$E$1,0))

 

[chingching831]

Thanks a lot everyone!!!

 

[etaf]

you are welcome