Return value based on certain criteria
 

Hi all,

I have two separate worksheets within the same workbook

The first worksheet (ConsultCall!) looks like this:

ID# Percentage

1-1232 50%
2-345 61%
10-12 89%
15-98753 25%

In another sheet (Calc!), I have a similar ID# column, but the ID #'s
are not in the same order as the first worksheet and some ID #'s are
in one that are not in the other column. Basically I want to be able
to match on unique ID #'s and then return the corresponding percentage
value in another column in the second worksheet for that ID # in the
first worksheet.

ID#
15-98753
10-12
2-345

Any thoughts? Thanks!

Return value based on certain criteria
 

Assuming:
A) the worksheet name does not actually include the !
B) the data in ConsultCall is in A1:B30
C) the date in Calc begins in A1

In B1 of Calc enter =VLOOKUP(A1,ConsultCall!$A$1:$B$30,2,FALSE)
But this will give an #N/A error when the ConsultCall does not have
corresponding ID

Use
IF(ISNA(VLOOKUP(A1,ConsultCall!$A$1:$B$30,2,FALSE) ),"X",VLOOKUP(A1,ConsultCall!$A$1:$B$30,2,FALSE) )
where X can be replaced by an message (or a blank)

In Excel 2007
IFERROR(VLOOKUP(A1,ConsultCall!$A$1:$B$30,2,FALSE) ),"X")

BEST WISHES
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP

"C." wrote in message
...
Hi all,

I have two separate worksheets within the same workbook

The first worksheet (ConsultCall!) looks like this:

ID# Percentage

1-1232 50%
2-345 61%
10-12 89%
15-98753 25%

In another sheet (Calc!), I have a similar ID# column, but the ID #'s
are not in the same order as the first worksheet and some ID #'s are
in one that are not in the other column. Basically I want to be able
to match on unique ID #'s and then return the corresponding percentage
value in another column in the second worksheet for that ID # in the
first worksheet.

ID#
15-98753
10-12
2-345

Any thoughts? Thanks!

Return value based on certain criteria
 

Hi,

use

=SUMPRODUCT(--(A1=ConsultCall!A1:A23),ConsultCall!B1:B23)

change your range to fit your needs

"C." wrote:

Hi all,

I have two separate worksheets within the same workbook

The first worksheet (ConsultCall!) looks like this:

ID# Percentage

1-1232 50%
2-345 61%
10-12 89%
15-98753 25%

In another sheet (Calc!), I have a similar ID# column, but the ID #'s
are not in the same order as the first worksheet and some ID #'s are
in one that are not in the other column. Basically I want to be able
to match on unique ID #'s and then return the corresponding percentage
value in another column in the second worksheet for that ID # in the
first worksheet.

ID#
15-98753
10-12
2-345

Any thoughts? Thanks!
.

Return value based on certain criteria
 

On Dec 18, 10:16*am, Eduardo
wrote:
Hi,

use

=SUMPRODUCT(--(A1=ConsultCall!A1:A23),ConsultCall!B1:B23)

change your range to fit your needs

"C." wrote:
Hi all,

I have two separate worksheets within the same workbook

The first worksheet (ConsultCall!) looks like this:

ID# * * * * * * * * Percentage

1-1232 * * * * * *50%
2-345 * * * * * * *61%
10-12 * * * * * * 89%
15-98753 * * * 25%

In another sheet (Calc!), I have a similar ID# column, but the ID #'s
are not in the same order as the first worksheet and some ID #'s are
in one that are not in the other column. Basically I want to be able
to match on unique ID #'s and then return the corresponding percentage
value in another column in the second worksheet for that ID # in the
first worksheet.

ID#
15-98753
10-12
2-345

Any thoughts? Thanks!
.

Thank you both! Works great!