Hi Richard,

I've tested that and it works a treat.

Thank you!



"RichardSchollar" wrote:

Hi

Try:

=INDEX($B$2:$B$9,MATCH(1,($A$2:$A$9=YourRank)*($C$ 2:$C
$9="YourLocation"),0))

Confirmed with Ctrl+Shift+Enter (it's an array formula). Following
successful entry, Excel will surround with curly braces {} - do not
enter these manually yourself.

Richard


On 4 Jan, 13:14, mr tom <mr-tom at mr-tom.co.uk.(donotspam) wrote:
Hi,

Happy New Year, all.

I need to lookup based on both of two conditions being satisfied.

We have a number of financial advisers in regional offices, and they are
ranked.

Given a rank and an office, I want to return the name of the adviser.

Don't worry about ranks that are too high or offices that don't exist.

I'm after something that says, if rank = 2 and office = London, return

Example Data List

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
And so on down, although in reality the list is unsorted.

In this case, it would return "John B" as Rank 2 in London

Does this make any sense?

Cheers.