Input the correct data when match

[Goddess]

Hi,

I need the row in yellow to be auto populated, whenever the data changes in cells I1:N2. Can't figure out the formula when it's neither car or bus (need to categorise all those under "others")

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Blue	Blue light	Blue Dark	Green	Green light	Green Dark		Blue	Blue light	Blue	Green	Green Dark	Green Light
2	Others	12	0	0	85	0	7		Van	Bus	Car	Car	Bike	Bus
3	Car	13	0	0	14	0	9		12	2	13	14	7	8
4	Bus	0	2	5	0	8	9
Sheet1

Thanks!

 

[etaf]

a few ways to do this

=IFERROR(INDEX($B$2:$G$4,MATCH(I2,$A$2:$A$4,0),MATCH(I1,$B$1:$G$1,0)),INDEX($B$2:$G$4,MATCH("others",$A$2:$A$4,0),MATCH(I1,$B$1:$G$1,0)))

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Blue	Blue light	Blue Dark	Green	Green light	Green Dark		Blue	Blue light	Blue	Green	Green Dark	Green Light
2	Others	12	0	0	85	0	7		Van	Bus	Car	Car	Bike	Bus
3	Car	13	0	0	14	0	9		12	2	13	14	7	8
4	Bus	0	2	5	0	8	9
Sheet1

Cell Formulas
Range	Formula
I3:N3	I3	=IFERROR(INDEX($B$2:$G$4,MATCH(I2,$A$2:$A$4,0),MATCH(I1,$B$1:$G$1,0)),INDEX($B$2:$G$4,MATCH("others",$A$2:$A$4,0),MATCH(I1,$B$1:$G$1,0)))

 

[StephenCrump]

Another ...

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Blue	Blue light	Blue Dark	Green	Green light	Green Dark		Blue	Blue light	Blue	Green	Green Dark	Green Light
2	Others	12	0	0	85	0	7		Van	Bus	Car	Car	Bike	Bus
3	Car	13	0	0	14	0	9		12	2	13	14	7	8
4	Bus	0	2	5	0	8	9
Sheet1

Cell Formulas
Range	Formula
I3:N3	I3	=XLOOKUP(I1,$B1:$G1,XLOOKUP(I2,$A2:$A4,$B2:$G4,$B2:$G2))

 

[Goddess]

Hi.. Thanks both for the quick response!

Stephen, just out of curiosity, will Xlookup work if I send this template to users who do not use office 2021? Basically what I'm trying to do is to create a master template, where my team mates will use for various projects. They will sometimes make adjustments to the template, depending on the requirements of each projects that they are working on.

 

[etaf]

No, from the reference site i use , it was introduce to office 2021 , and also will be in 365 subscription version

Excel Functions 365 New Functions

Excel Reference - Microsoft Office Add-ins and Consultancy. One website for all Microsoft Office Users and Developers.

bettersolutions.com