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?

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?

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?

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

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?

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?

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?