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?

Hi!

Try something like this:

A1 = some acct #

B1:

=IF(COUNTIF(Sheet2!Acct_#_Range,A1),INDEX(Sheet2!Acct_title_range,MATCH(A1,Sheet2!Acct_#_range,0)),"")

Biff

"Ash" <[email protected]> wrote in message
news:[email protected]...
>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?

use the MATCH function to find the row and incorporate that into an INDEX
function for the value needed.

--
Don Guillett
SalesAid Software
[email protected]
"Ash" <[email protected]> wrote in message
news:[email protected]...
>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?

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

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!Acct_title_range,MATCH(A1,Sheet2!Acct_#_range,0)),"")
>
> Biff
>
> "Ash" <[email protected]> wrote in message
> news:[email protected]...
> >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?
>
>
>

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!Acct_title_range,MATCH(A1,Sheet2!Acct_#_range,0)),"")
>
> Biff
>
> "Ash" <[email protected]> wrote in message
> news:[email protected]...
> >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?
>
>
>

>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" <[email protected]> wrote in message
news:[email protected]...
> 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!Acct_title_range,MATCH(A1,Sheet2!Acct_#_range,0)),"")
>>
>> Biff
>>
>> "Ash" <[email protected]> wrote in message
>> news:[email protected]...
>> >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?
>>
>>
>>