Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup w/ AND function
I have a large worksheet and the 1st column is accounts and the 2nd column is
sub accounts, now there are some accounts that are the repeats and some subaccounts that are repeats, but never in the same row. I know how to search using the AND function to find the unique account and subaccount combination, but is there a way to return the value found by this combination? All I am getting is TRUE. SAVE ME Excel Gods!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup w/ AND function
Is the value to be returned numeric or text or can it be either?
Are you saying that the combination of the account and subaccount are unique? Here's a generic method that returns either text or numbers. Array entered** : =INDEX(C1:C10,MATCH(1,(A1:A10=account)*(B1:B10=sub account),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "dannyboy8" wrote in message ... I have a large worksheet and the 1st column is accounts and the 2nd column is sub accounts, now there are some accounts that are the repeats and some subaccounts that are repeats, but never in the same row. I know how to search using the AND function to find the unique account and subaccount combination, but is there a way to return the value found by this combination? All I am getting is TRUE. SAVE ME Excel Gods!!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup w/ AND function
Biff in this case it is a #, here's an example of what I mean:
Account Subaccount Balance 305000 00-00-000-EQ-00-RS051 # 305000 00-00-000-EQ-00-RS052 # 305500 00-00-000-EQ-00-RS051 # 305500 00-00-000-EQ-00-RS052 # As you can see there are like acts and subs, but the combo of the 2 are unique, would you formula work to return the balance (separate column) based on the combinations? "T. Valko" wrote: Is the value to be returned numeric or text or can it be either? Are you saying that the combination of the account and subaccount are unique? Here's a generic method that returns either text or numbers. Array entered** : =INDEX(C1:C10,MATCH(1,(A1:A10=account)*(B1:B10=sub account),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "dannyboy8" wrote in message ... I have a large worksheet and the 1st column is accounts and the 2nd column is sub accounts, now there are some accounts that are the repeats and some subaccounts that are repeats, but never in the same row. I know how to search using the AND function to find the unique account and subaccount combination, but is there a way to return the value found by this combination? All I am getting is TRUE. SAVE ME Excel Gods!!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup w/ AND function
would you formula work to return the balance (separate column)
based on the combinations? Yes, but this one is "less confusing" : E1 = some account number F1 = some subaccount number =SUMPRODUCT(--(A1:A10=E1),--(B1:B10=F1),C1:C10) -- Biff Microsoft Excel MVP "dannyboy8" wrote in message ... Biff in this case it is a #, here's an example of what I mean: Account Subaccount Balance 305000 00-00-000-EQ-00-RS051 # 305000 00-00-000-EQ-00-RS052 # 305500 00-00-000-EQ-00-RS051 # 305500 00-00-000-EQ-00-RS052 # As you can see there are like acts and subs, but the combo of the 2 are unique, would you formula work to return the balance (separate column) based on the combinations? "T. Valko" wrote: Is the value to be returned numeric or text or can it be either? Are you saying that the combination of the account and subaccount are unique? Here's a generic method that returns either text or numbers. Array entered** : =INDEX(C1:C10,MATCH(1,(A1:A10=account)*(B1:B10=sub account),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "dannyboy8" wrote in message ... I have a large worksheet and the 1st column is accounts and the 2nd column is sub accounts, now there are some accounts that are the repeats and some subaccounts that are repeats, but never in the same row. I know how to search using the AND function to find the unique account and subaccount combination, but is there a way to return the value found by this combination? All I am getting is TRUE. SAVE ME Excel Gods!!! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup w/ AND function
Biff, that worked great!, got 1 quick question, what is the functionality of
the dashes?? (--) "T. Valko" wrote: would you formula work to return the balance (separate column) based on the combinations? Yes, but this one is "less confusing" : E1 = some account number F1 = some subaccount number =SUMPRODUCT(--(A1:A10=E1),--(B1:B10=F1),C1:C10) -- Biff Microsoft Excel MVP "dannyboy8" wrote in message ... Biff in this case it is a #, here's an example of what I mean: Account Subaccount Balance 305000 00-00-000-EQ-00-RS051 # 305000 00-00-000-EQ-00-RS052 # 305500 00-00-000-EQ-00-RS051 # 305500 00-00-000-EQ-00-RS052 # As you can see there are like acts and subs, but the combo of the 2 are unique, would you formula work to return the balance (separate column) based on the combinations? "T. Valko" wrote: Is the value to be returned numeric or text or can it be either? Are you saying that the combination of the account and subaccount are unique? Here's a generic method that returns either text or numbers. Array entered** : =INDEX(C1:C10,MATCH(1,(A1:A10=account)*(B1:B10=sub account),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "dannyboy8" wrote in message ... I have a large worksheet and the 1st column is accounts and the 2nd column is sub accounts, now there are some accounts that are the repeats and some subaccounts that are repeats, but never in the same row. I know how to search using the AND function to find the unique account and subaccount combination, but is there a way to return the value found by this combination? All I am getting is TRUE. SAVE ME Excel Gods!!! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup w/ AND function
SUMPRODUCT multiplies arrays of numbers together to get a result.
Each of these expressions will return an array of either TRUE or FALSE: A1:A10=E1 B1:B10=F1 The double unary minus "--" is used to coerce those TRUE and FALSE to numbers that SUMPRODUCT can then use to calculate the result. --TRUE gets coerced to numeric 1 and --FALSE gets coerced to numeric 0. See this for a comprehensive analysis of SUMPRODUCT: http://xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "dannyboy8" wrote in message ... Biff, that worked great!, got 1 quick question, what is the functionality of the dashes?? (--) "T. Valko" wrote: would you formula work to return the balance (separate column) based on the combinations? Yes, but this one is "less confusing" : E1 = some account number F1 = some subaccount number =SUMPRODUCT(--(A1:A10=E1),--(B1:B10=F1),C1:C10) -- Biff Microsoft Excel MVP "dannyboy8" wrote in message ... Biff in this case it is a #, here's an example of what I mean: Account Subaccount Balance 305000 00-00-000-EQ-00-RS051 # 305000 00-00-000-EQ-00-RS052 # 305500 00-00-000-EQ-00-RS051 # 305500 00-00-000-EQ-00-RS052 # As you can see there are like acts and subs, but the combo of the 2 are unique, would you formula work to return the balance (separate column) based on the combinations? "T. Valko" wrote: Is the value to be returned numeric or text or can it be either? Are you saying that the combination of the account and subaccount are unique? Here's a generic method that returns either text or numbers. Array entered** : =INDEX(C1:C10,MATCH(1,(A1:A10=account)*(B1:B10=sub account),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "dannyboy8" wrote in message ... I have a large worksheet and the 1st column is accounts and the 2nd column is sub accounts, now there are some accounts that are the repeats and some subaccounts that are repeats, but never in the same row. I know how to search using the AND function to find the unique account and subaccount combination, but is there a way to return the value found by this combination? All I am getting is TRUE. SAVE ME Excel Gods!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Embedded VLOOKUP function within IF function | Excel Worksheet Functions | |||
HOW DO I NEST THE VLOOKUP FUNCTION WITH THE LEFT FUNCTION | Excel Worksheet Functions | |||
how do I write a vlookup function within an iserror function so t. | Excel Worksheet Functions | |||
I want to use Vlookup function and AND function in a single formu. | Excel Worksheet Functions |