I am not sure I have understood correectly. Please go through the below example

With data as below if you need to retrive the name of the 1st Rank holder
from London.

D2 = 1
D3 = London

In D4 apply the below formula
=INDEX($B$2:$B$9,MATCH(1,($A$2:$A$9=D2)*($C$2:$C$9 =D3),0))

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}"

ColA ColB ColC
Rank Name Office
1 Tom R Bath
2 Katy C Bath
3 Nigel G Bath
4 Pete R Bath
1 Tony A London
2 John B London
3 Mary C London
4 Jane D London


--
Jacob (MVP - Excel)


"K" wrote:

Hi all, does any friend know that how can I make below formula work
MATCH(A2,$K$2:$M$30,0)
.