| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLookup with multiple conditions
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLookup with multiple conditions
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLookup with multiple conditions
=INDEX(B2:B9,MATCH(E2&E3,A2:A9&C2:C9,0))
"mr tom" skrev: 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLookup with multiple conditions
forgot that E2 is number and E3 is sity
"excelent" skrev: =INDEX(B2:B9,MATCH(E2&E3,A2:A9&C2:C9,0)) "mr tom" skrev: 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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLookup with multiple conditions
Hi, excelent
I've already got Richard's solution running - but yours looks every bit as suitable. Many thanks! Tom. "excelent" wrote: =INDEX(B2:B9,MATCH(E2&E3,A2:A9&C2:C9,0)) "mr tom" skrev: 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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLookup with multiple conditions
Hi,
Excelent's formula is also an array formula : Ctrl+Shift+Enter instead of Enter ... |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 Carim wrote: 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: On Friday, January 04, 2008 8:14 AM mr-tom at mr-tom.co.uk.(donotspam) wrote: 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 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. On Friday, January 04, 2008 9:02 AM excelen wrote: VLookup with multiple conditions =INDEX(B2:B9,MATCH(E2&E3,A2:A9&C2:C9,0)) "mr tom" skrev: On Friday, January 04, 2008 9:04 AM mr-tom at mr-tom.co.uk.(donotspam) wrote: 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! "RichardSchollar" wrote: On Friday, January 04, 2008 9:05 AM excelen wrote: VLookup with multiple conditions forgot that E2 is number and E3 is sity "excelent" skrev: On Friday, January 04, 2008 9:06 AM mr-tom at mr-tom.co.uk.(donotspam) wrote: Hi, excelentI've already got Richard's solution running - but yours looks Hi, excelent I have already got Richard's solution running - but yours looks every bit as suitable. Many thanks! Tom. "excelent" wrote: On Saturday, January 05, 2008 4:46 AM RichardSchollar wrote: VLookup with multiple conditions Hi Try: =3DINDEX($B$2:$B$9,MATCH(1,($A$2:$A$9=3DYourRank)* ($C$2:$C $9=3D"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: On Saturday, January 05, 2008 4:46 AM Carim wrote: 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/tutorials...s-with-st.aspx |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Hi to all
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" wrote in message ... 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: 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: On Friday, January 04, 2008 8:14 AM mr-tom at mr-tom.co.uk.(donotspam) wrote: 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 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. On Friday, January 04, 2008 9:02 AM excelen wrote: VLookup with multiple conditions =INDEX(B2:B9,MATCH(E2&E3,A2:A9&C2:C9,0)) "mr tom" skrev: On Friday, January 04, 2008 9:04 AM mr-tom at mr-tom.co.uk.(donotspam) wrote: 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! "RichardSchollar" wrote: On Friday, January 04, 2008 9:05 AM excelen wrote: VLookup with multiple conditions forgot that E2 is number and E3 is sity "excelent" skrev: On Friday, January 04, 2008 9:06 AM mr-tom at mr-tom.co.uk.(donotspam) wrote: Hi, excelentI've already got Richard's solution running - but yours looks Hi, excelent I have already got Richard's solution running - but yours looks every bit as suitable. Many thanks! Tom. "excelent" wrote: On Saturday, January 05, 2008 4:46 AM RichardSchollar wrote: VLookup with multiple conditions Hi Try: =3DINDEX($B$2:$B$9,MATCH(1,($A$2:$A$9=3DYourRank)* ($C$2:$C $9=3D"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: On Saturday, January 05, 2008 4:46 AM Carim wrote: 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/tutorials...s-with-st.aspx |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Hi
HI i need help in time keeping
Carim wrote: 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: On Friday, January 04, 2008 8:14 AM mr-tom at mr-tom.co.uk.(donotspam) wrote: 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 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. On Friday, January 04, 2008 9:02 AM excelen wrote: VLookup with multiple conditions =INDEX(B2:B9,MATCH(E2&E3,A2:A9&C2:C9,0)) "mr tom" skrev: On Friday, January 04, 2008 9:04 AM mr-tom at mr-tom.co.uk.(donotspam) wrote: 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! "RichardSchollar" wrote: On Friday, January 04, 2008 9:05 AM excelen wrote: VLookup with multiple conditions forgot that E2 is number and E3 is sity "excelent" skrev: On Friday, January 04, 2008 9:06 AM mr-tom at mr-tom.co.uk.(donotspam) wrote: Hi, excelentI've already got Richard's solution running - but yours looks Hi, excelent I have already got Richard's solution running - but yours looks every bit as suitable. Many thanks! Tom. "excelent" wrote: On Saturday, January 05, 2008 4:46 AM RichardSchollar wrote: VLookup with multiple conditions Hi Try: =3DINDEX($B$2:$B$9,MATCH(1,($A$2:$A$9=3DYourRank)* ($C$2:$C $9=3D"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: On Saturday, January 05, 2008 4:46 AM Carim wrote: VLookup with multiple conditions Hi, Excelent's formula is also an array formula : Ctrl+Shift+Enter instead of Enter ... On Saturday, January 09, 2010 2:23 AM anthony alvarez wrote: 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/tutorials...cfenabled.aspx |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple rec | Excel Discussion (Misc queries) | |||
| VLOOKUP with multiple conditions | Excel Discussion (Misc queries) | |||
| Combining Text from multiple cells under multiple conditions | Excel Worksheet Functions | |||
| Vlookup with multiple conditions | Excel Worksheet Functions | |||
| How to multiple conditions to validate more than 2 conditions to . | Excel Worksheet Functions | |||
Hybrid Mode
