Nested Index Match in Power Query

hi,

do you know how to perform nested Index Match in Power Query?

Start looking in 1st column, then narrow looking in 2nd column, and then give a result from the 3th column. For example 11 gives aaa and 4B gives fff.

One more thing, can I make it without merging columns 1 and 2?

No	Index	Description
1	1	aaa
1	2	bbb
2	R	ccc
3	1	ddd
4	A	eee
4	B	fff

Here...

Replace 1& 1 with reference cells (ie E1&F1)

=INDEX($C$5:$C$22,MATCH(1&1,$A$5:$A$22&$B$5:$B$22,0))

note that better approach is with some delimiter like for example |

=INDEX($C$5:$C$22,MATCH(E1&"|"&F1,$A$5:$A$22&"|"&$B$5:$B$22,0))

Previous solution will fail if you have overlapping criteria.

For example: 1 & 11 and 11 & 1 will give wrong result in first approach, and good result in second.

Originally Posted by zbor

note that better approach is with some delimiter like for example |

=INDEX($C$5:$C$22,MATCH(E1&"|"&F1,$A$5:$A$22&"|"&$B$5:$B$22,0))

Previous solution will fail if you have overlapping criteria.

For example: 1 & 11 and 11 & 1 will give wrong result in first approach, and good result in second.

hi, Ia m looking for a Power Query solution.