Search Table

[ronie85]

Hi, I am looking a formula to search a table say D and 5 to return "Red" if someone could help please.

 

[Fluff]

how about

=index(b2:g8,xmatch(5,a2:a8),xmatch("D",b1:g1))

 

[ronie85]

This works amazing thank you very much

 

[Fluff]

You're welcome & thanks for the feedback.

 

[ronie85]

I hope you can help further, I've now used the formula in another way and need to incorporate the 3rd scenario. D, 5, Apr to show "Red".

This has worked in my new row but when filled, it shows Ref therefore something isn't working correct. Maybe it is the order in which I have wrote it.

This is the formula I am using however the table further below is to simplify it so that I can understand it simply and apply it.

=INDEX('Approved Payroll'!$B$3:$AG$6,XMATCH(C2,'Approved Payroll'!$A$3:$A$6),XMATCH(B2,'Approved Payroll'!$B$1:$K$1),XMATCH($E$1,'Approved Payroll'!$B$2:$K$2))

	A	B	C	D	E	F
	Jan	Feb	Mar	Apr	May	Jun
1	Grey	Purple	Black	Orange	Blue	Green
2	Red	Grey	Purple	Black	Orange	Blue
3	Green	Red	Grey	Purple	Black	Orange
4	Blue	Green	Red	Grey	Purple	Black
5	Orange	Blue	Green	Red	Grey	Purple
6	Black	Orange	Blue	Green	Red	Grey
7	Purple	Black	Orange	Blue	Green	Red

 

[Fluff]

As D & Apr are on the same column you can just match one of them.

 

[ronie85]

Would there be any way to match 3 if i changed the layout? It did work for one however that may have been luck

 

[Fluff]

How about

=INDEX('Approved Payroll'!$B$3:$AG$6,XMATCH(C2,'Approved Payroll'!$A$3:$A$6),XMATCH(B2&"|"&E1,'Approved Payroll'!$B$1:$K$1&"|"&'Approved Payroll'!$B$2:$K$2))