Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex logical/conditional formula
I am working between two worksheets. In one worksheet I have a list of
account numbers. In the second worksheet I have the same account numbers in a different order. What I am trying to do is, If the account number from worksheet one is found in the column with the account numbers in worksheet two, I want to display the matching title for the account number that is in the same row but different column in worksheet two. So essentially (if acct # from column x in wks 1= an acct in number in column m from wks 2, display the corresponding title in the same row of the matching acct # found in column m). Is this possible? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex logical/conditional formula
use the MATCH function to find the row and incorporate that into an INDEX
function for the value needed. -- Don Guillett SalesAid Software "Ash" wrote in message ... I am working between two worksheets. In one worksheet I have a list of account numbers. In the second worksheet I have the same account numbers in a different order. What I am trying to do is, If the account number from worksheet one is found in the column with the account numbers in worksheet two, I want to display the matching title for the account number that is in the same row but different column in worksheet two. So essentially (if acct # from column x in wks 1= an acct in number in column m from wks 2, display the corresponding title in the same row of the matching acct # found in column m). Is this possible? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex logical/conditional formula
Hi!
Try something like this: A1 = some acct # B1: =IF(COUNTIF(Sheet2!Acct_#_Range,A1),INDEX(Sheet2!A cct_title_range,MATCH(A1,Sheet2!Acct_#_range,0))," ") Biff "Ash" wrote in message ... I am working between two worksheets. In one worksheet I have a list of account numbers. In the second worksheet I have the same account numbers in a different order. What I am trying to do is, If the account number from worksheet one is found in the column with the account numbers in worksheet two, I want to display the matching title for the account number that is in the same row but different column in worksheet two. So essentially (if acct # from column x in wks 1= an acct in number in column m from wks 2, display the corresponding title in the same row of the matching acct # found in column m). Is this possible? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex logical/conditional formula
ok assuming your sheets are Sheet1 and Sheet2 and for the sake of this
exercise that both of the account number columns are "A" and that the title is in column "B" of Sheet2 try this: in sheet1 in the cell that you want to return the data place this formula: =VLOOKUP(A1,Sheet2!A:B,2) This will get the value of cell "A1" in Sheet1, then find it in the array you gave it to search (Sheet2!A:B) and return the value in the same row but column two of the match. the only catch here being that you must have the column with the account number at the far left of the search range. HTH Die_Another_Day put this next to Ash wrote: I am working between two worksheets. In one worksheet I have a list of account numbers. In the second worksheet I have the same account numbers in a different order. What I am trying to d |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex logical/conditional formula
Thank you!! This worked like a charm!
"Biff" wrote: Hi! Try something like this: A1 = some acct # B1: =IF(COUNTIF(Sheet2!Acct_#_Range,A1),INDEX(Sheet2!A cct_title_range,MATCH(A1,Sheet2!Acct_#_range,0))," ") Biff "Ash" wrote in message ... I am working between two worksheets. In one worksheet I have a list of account numbers. In the second worksheet I have the same account numbers in a different order. What I am trying to do is, If the account number from worksheet one is found in the column with the account numbers in worksheet two, I want to display the matching title for the account number that is in the same row but different column in worksheet two. So essentially (if acct # from column x in wks 1= an acct in number in column m from wks 2, display the corresponding title in the same row of the matching acct # found in column m). Is this possible? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex logical/conditional formula
Okay, I spoke to soon. I'm trying to copy the formula down, however, with
Excel, it automatically is changing the range on Sheet 2 to the next down, so instead of it staying a constant Range a1-a5, its changing to a2-a6 and so forth. Any suggestions? "Biff" wrote: Hi! Try something like this: A1 = some acct # B1: =IF(COUNTIF(Sheet2!Acct_#_Range,A1),INDEX(Sheet2!A cct_title_range,MATCH(A1,Sheet2!Acct_#_range,0))," ") Biff "Ash" wrote in message ... I am working between two worksheets. In one worksheet I have a list of account numbers. In the second worksheet I have the same account numbers in a different order. What I am trying to do is, If the account number from worksheet one is found in the column with the account numbers in worksheet two, I want to display the matching title for the account number that is in the same row but different column in worksheet two. So essentially (if acct # from column x in wks 1= an acct in number in column m from wks 2, display the corresponding title in the same row of the matching acct # found in column m). Is this possible? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex logical/conditional formula
instead of it staying a constant Range a1-a5, its changing to a2-a6 and so
forth. Any suggestions? Try it like this: I'll use arbitrary references: =IF(COUNTIF(Sheet2!A$1:A$5,A1),INDEX(Sheet2!A$1:A$ 5,MATCH(A1,Sheet2!A$1:A$5,0)),"") Biff "Ash" wrote in message ... Okay, I spoke to soon. I'm trying to copy the formula down, however, with Excel, it automatically is changing the range on Sheet 2 to the next down, so instead of it staying a constant Range a1-a5, its changing to a2-a6 and so forth. Any suggestions? "Biff" wrote: Hi! Try something like this: A1 = some acct # B1: =IF(COUNTIF(Sheet2!Acct_#_Range,A1),INDEX(Sheet2!A cct_title_range,MATCH(A1,Sheet2!Acct_#_range,0))," ") Biff "Ash" wrote in message ... I am working between two worksheets. In one worksheet I have a list of account numbers. In the second worksheet I have the same account numbers in a different order. What I am trying to do is, If the account number from worksheet one is found in the column with the account numbers in worksheet two, I want to display the matching title for the account number that is in the same row but different column in worksheet two. So essentially (if acct # from column x in wks 1= an acct in number in column m from wks 2, display the corresponding title in the same row of the matching acct # found in column m). Is this possible? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A formula that is too complex for Excel? | Excel Worksheet Functions | |||
Complex Formula Getting Error.. | Excel Discussion (Misc queries) | |||
2 Nesting questions | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Complex formula | Excel Discussion (Misc queries) |