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.