VLookup with multiple conditions

[mr tom]

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.

 

[RichardSchollar]

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

 

[excelent]

=INDEX(B2:B9,MATCH(E2&E3,A2:A9&C2:C9,0))


"mr tom" skrev:

 

[mr tom]

Hi Richard,

I've tested that and it works a treat.

Thank you!

 

[excelent]

forgot that E2 is number and E3 is sity


"excelent" skrev:

 

[mr tom]

Hi, excelent

I've already got Richard's solution running - but yours looks every bit as
suitable.

Many thanks!


Tom.

 

[Carim]

Hi,

Excelent's formula is also an array formula :
Ctrl+Shift+Enter instead of Enter ...

 

[anthony alvarez]

I am doing a vlookup with regard to time keeping

A B C D
ID Date Time Status
0001 01/01/2010 7:25AM IN
0002 01/01/2010 8:12AM IN
0001 01/01/2010 5:15PM OUT
0002 01/01/2010 6:45PM OUT

I want to have an out put like


Date Time IN Time OUT
01/01/2010 7:25AM 5:15PM

Can anybody help me please.. Best regards,Anthony



Carim wrote:

Re: VLookup with multiple conditions
05-Jan-08

Hi

Excelent's formula is also an array formula
Ctrl+Shift+Enter instead of Enter ...

Previous Posts In This Thread:

VLookup with multiple conditions
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 Lis

Rank Name Offic
1 Tom R Bat
2 Katy C Bat
3 Nigel G Bat
4 Pete R Bat
1 Tony A Londo
2 John B Londo
3 Mary C Londo
4 Jane D Londo
And so on down, although in reality the list is unsorted

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

Does this make any sense

Cheers.

RE: VLookup with multiple conditions
=INDEX(B2:B9,MATCH(E2&E3,A2:A9&C2:C9,0)

"mr tom" skrev:

Hi Richard,I've tested that and it works a treat.Thank you!
Hi Richard

I have tested that and it works a treat

Thank you


:

RE: VLookup with multiple conditions
forgot that E2 is number and E3 is sit

"excelent" skrev:

Hi, excelentI've already got Richard's solution running - but yours looks
Hi, excelen

I have already got Richard's solution running - but yours looks every bit a
suitable

Many thanks

Tom


:

Re: VLookup with multiple conditions
H

Try

=3DINDEX($B$2:$B$9,MATCH(1,($A$2:$A$9=3DYourRank)*($C$2:$
$9=3D"YourLocation"),0)

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

Richar

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

Re: VLookup with multiple conditions
Hi

Excelent's formula is also an array formula
Ctrl+Shift+Enter instead of Enter ...


Submitted via EggHeadCafe - Software Developer Portal of Choice
Output Parameters with Stored Proc ADO.NET
http://www.eggheadcafe.com/tutorial...a-fceae9f9d99e/output-parameters-with-st.aspx

 

[Ashish Mathur]

Hi,

If you wish to know the largest time for the person, you may use the
following array formula (Ctrl+Shift+Enter)

=max(if(($A$2:$a$5=$A7),$D$2:$D$5))

A2:A5 has the data. A7 has 0001

Time in would be a simple vlookup (which you are already using)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[anthony alvarez]

HI i need help in time keeping



Carim wrote:

Re: VLookup with multiple conditions
05-Jan-08

Hi

Excelent's formula is also an array formula
Ctrl+Shift+Enter instead of Enter ...

Previous Posts In This Thread:

VLookup with multiple conditions
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 Lis

Rank Name Offic
1 Tom R Bat
2 Katy C Bat
3 Nigel G Bat
4 Pete R Bat
1 Tony A Londo
2 John B Londo
3 Mary C Londo
4 Jane D Londo
And so on down, although in reality the list is unsorted

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

Does this make any sense

Cheers.

RE: VLookup with multiple conditions
=INDEX(B2:B9,MATCH(E2&E3,A2:A9&C2:C9,0)

"mr tom" skrev:

Hi Richard,I've tested that and it works a treat.Thank you!
Hi Richard

I have tested that and it works a treat

Thank you


:

RE: VLookup with multiple conditions
forgot that E2 is number and E3 is sit

"excelent" skrev:

Hi, excelentI've already got Richard's solution running - but yours looks
Hi, excelen

I have already got Richard's solution running - but yours looks every bit a
suitable

Many thanks

Tom


:

Re: VLookup with multiple conditions
H

Try

=3DINDEX($B$2:$B$9,MATCH(1,($A$2:$A$9=3DYourRank)*($C$2:$
$9=3D"YourLocation"),0)

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

Richar

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

Re: VLookup with multiple conditions
Hi

Excelent's formula is also an array formula
Ctrl+Shift+Enter instead of Enter ...

Hi to all
I am doing a vlookup with regard to time keeping

A B C D
ID Date Time Status
0001 01/01/2010 7:25AM IN
0002 01/01/2010 8:12AM IN
0001 01/01/2010 5:15PM OUT
0002 01/01/2010 6:45PM OUT

I want to have an out put like


Date Time IN Time OUT
01/01/2010 7:25AM 5:15PM

Can anybody help me please.. Best regards,Anthony


Submitted via EggHeadCafe - Software Developer Portal of Choice
Silverlight 3 WCF-Enabled CodeSnippet app with Amazon SimpleDb
http://www.eggheadcafe.com/tutorial...0d-068142bff1a9/silverlight-3-wcfenabled.aspx