Return Corresponding Value Based on Comparing Two Sheets of Data
 

Can someone please help me find a formula (or two) for this example. If you
can show me a couple of ways to do this (so I can learn), I'd greatly
appreciate it! Thanks!

I'm trying to figure out a formula (or two) that will help me auto-populate
the ? fields within ColumnB of Sheet 2. Also, I want the ? value for the
"8888" row to return blank, since it does not exist in Sheet 1.

Sheet 1:

ColumnA ColumnB

1234 20
4321 10
5678 11
8765 -
9999 12
7777 13

Sheet 2:

ColumnA ColumnB

5678 ?
8765 ?
1234 ?
4321 ?
8888 ?

Return Corresponding Value Based on Comparing Two Sheets of Data
 

Sheet 1:
ColumnA ColumnB
8765 -

Is that "dash" entered in the cell or does it represent an empty cell?

Sheet 2:
ColumnA ColumnB
8765 ?

So, what result should appear on Sheet2 for 8765?

--
Biff
Microsoft Excel MVP


"PaulQ" wrote in message
...
Can someone please help me find a formula (or two) for this example. If
you
can show me a couple of ways to do this (so I can learn), I'd greatly
appreciate it! Thanks!

I'm trying to figure out a formula (or two) that will help me
auto-populate
the ? fields within ColumnB of Sheet 2. Also, I want the ? value for the
"8888" row to return blank, since it does not exist in Sheet 1.

Sheet 1:

ColumnA ColumnB

1234 20
4321 10
5678 11
8765 -
9999 12
7777 13

Sheet 2:

ColumnA ColumnB

5678 ?
8765 ?
1234 ?
4321 ?
8888 ?

Return Corresponding Value Based on Comparing Two Sheets of Data
 

This should do what you want:
=if(isna(vlookup(a1,sheet1!A:B,2,false)),"",vlooku p(a1,sheet1!A:B,2,false))

Regards,
Fred

"PaulQ" wrote in message
...
Can someone please help me find a formula (or two) for this example. If
you
can show me a couple of ways to do this (so I can learn), I'd greatly
appreciate it! Thanks!

I'm trying to figure out a formula (or two) that will help me
auto-populate
the ? fields within ColumnB of Sheet 2. Also, I want the ? value for the
"8888" row to return blank, since it does not exist in Sheet 1.

Sheet 1:

ColumnA ColumnB

1234 20
4321 10
5678 11
8765 -
9999 12
7777 13

Sheet 2:

ColumnA ColumnB

5678 ?
8765 ?
1234 ?
4321 ?
8888 ?

Return Corresponding Value Based on Comparing Two Sheets of Da
 

The dash is an actual value. That is, the value returned for row 8765 should
be "-". Thanks!

"T. Valko" wrote:

Sheet 1:
ColumnA ColumnB
8765 -

Is that "dash" entered in the cell or does it represent an empty cell?

Sheet 2:
ColumnA ColumnB
8765 ?

So, what result should appear on Sheet2 for 8765?

--
Biff
Microsoft Excel MVP


"PaulQ" wrote in message
...
Can someone please help me find a formula (or two) for this example. If
you
can show me a couple of ways to do this (so I can learn), I'd greatly
appreciate it! Thanks!

I'm trying to figure out a formula (or two) that will help me
auto-populate
the ? fields within ColumnB of Sheet 2. Also, I want the ? value for the
"8888" row to return blank, since it does not exist in Sheet 1.

Sheet 1:

ColumnA ColumnB

1234 20
4321 10
5678 11
8765 -
9999 12
7777 13

Sheet 2:

ColumnA ColumnB

5678 ?
8765 ?
1234 ?
4321 ?
8888 ?


.

Return Corresponding Value Based on Comparing Two Sheets of Da
 

That worked perfectly! Thanks, Fred!

Bill, feel free to give your suggestions as well. Again, as a learning
opportunity for me (and others) and for a different perspective. Thanks! You
guys are great!

"Fred Smith" wrote:

This should do what you want:
=if(isna(vlookup(a1,sheet1!A:B,2,false)),"",vlooku p(a1,sheet1!A:B,2,false))

Regards,
Fred

"PaulQ" wrote in message
...
Can someone please help me find a formula (or two) for this example. If
you
can show me a couple of ways to do this (so I can learn), I'd greatly
appreciate it! Thanks!

I'm trying to figure out a formula (or two) that will help me
auto-populate
the ? fields within ColumnB of Sheet 2. Also, I want the ? value for the
"8888" row to return blank, since it does not exist in Sheet 1.

Sheet 1:

ColumnA ColumnB

1234 20
4321 10
5678 11
8765 -
9999 12
7777 13

Sheet 2:

ColumnA ColumnB

5678 ?
8765 ?
1234 ?
4321 ?
8888 ?

.

Return Corresponding Value Based on Comparing Two Sheets of Da
 

You're welcome. Thanks for the feedback.

Regards,
Fred

"PaulQ" wrote in message
...
That worked perfectly! Thanks, Fred!

Bill, feel free to give your suggestions as well. Again, as a learning
opportunity for me (and others) and for a different perspective. Thanks!
You
guys are great!

"Fred Smith" wrote:

This should do what you want:
=if(isna(vlookup(a1,sheet1!A:B,2,false)),"",vlooku p(a1,sheet1!A:B,2,false))

Regards,
Fred

"PaulQ" wrote in message
...
Can someone please help me find a formula (or two) for this example. If
you
can show me a couple of ways to do this (so I can learn), I'd greatly
appreciate it! Thanks!

I'm trying to figure out a formula (or two) that will help me
auto-populate
the ? fields within ColumnB of Sheet 2. Also, I want the ? value for
the
"8888" row to return blank, since it does not exist in Sheet 1.

Sheet 1:

ColumnA ColumnB

1234 20
4321 10
5678 11
8765 -
9999 12
7777 13

Sheet 2:

ColumnA ColumnB

5678 ?
8765 ?
1234 ?
4321 ?
8888 ?

.

Return Corresponding Value Based on Comparing Two Sheets of Da
 

show me a couple of ways to do this (so I can learn)

Ok, here'a bunch of examples. This is a good demonstration that shows just
how many different ways you can do something.

Let's assume your data is setup like this:

A2:B7 -

1234 20
4321 10
5678 11
8765 -
9999 12
7777 13

A10:A14 -

5678
8765
1234
4321
8888

Enter any one of these formulas in B10 and copy down to B14:

=IF(ISNA(VLOOKUP(A10,A$2:B$7,2,0)),"",VLOOKUP(A10, A$2:B$7,2,0))

=IF(ISERROR(VLOOKUP(A10,A$2:B$7,2,0)),"",VLOOKUP(A 10,A$2:B$7,2,0))

=IF(ISNA(MATCH(A10,A$2:A$7,0)),"",VLOOKUP(A10,A$2: B$7,2,0))

=IF(ISERROR(MATCH(A10,A$2:A$7,0)),"",VLOOKUP(A10,A $2:B$7,2,0))

=IF(ISNUMBER(MATCH(A10,A$2:A$7,0)),VLOOKUP(A10,A$2 :B$7,2,0),"")

=IF(COUNT(MATCH(A10,A$2:A$7,0)),VLOOKUP(A10,A$2:B$ 7,2,0),"")

=IF(COUNTIF(A$2:B$7,A10),VLOOKUP(A10,A$2:B$7,2,0), "")

=IF(ISNA(MATCH(A10,A$2:A$7,0)),"",INDEX(B$2:B$7,MA TCH(A10,A$2:A$7,0)))

=IF(ISERROR(MATCH(A10,A$2:A$7,0)),"",INDEX(B$2:B$7 ,MATCH(A10,A$2:A$7,0)))

=IF(ISNUMBER(MATCH(A10,A$2:A$7,0)),INDEX(B$2:B$7,M ATCH(A10,A$2:A$7,0)),"")

=IF(COUNT(MATCH(A10,A$2:A$7,0)),INDEX(B$2:B$7,MATC H(A10,A$2:A$7,0)),"")

=IF(COUNTIF(A$2:A$7,A10),INDEX(B$2:B$7,MATCH(A10,A $2:A$7,0)),"")

=IF(COUNTIF(A$2:A$7,A10),OFFSET(B$2,MATCH(A10,A$2: A$7,0)-1,0),"")

=IF(ISNA(MATCH(A10,A$2:A$7,0)),"",OFFSET(B$2,MATCH (A10,A$2:A$7,0)-1,0))

=IF(ISERROR(MATCH(A10,A$2:A$7,0)),"",OFFSET(B$2,MA TCH(A10,A$2:A$7,0)-1,0))

=IF(ISNUMBER(MATCH(A10,A$2:A$7,0)),OFFSET(B$2,MATC H(A10,A$2:A$7,0)-1,0),"")

=IF(COUNT(MATCH(A10,A$2:A$7,0)),OFFSET(B$2,MATCH(A 10,A$2:A$7,0)-1,0),"")

I might have missed a few!

--
Biff
Microsoft Excel MVP


"PaulQ" wrote in message
...
The dash is an actual value. That is, the value returned for row 8765
should
be "-". Thanks!

"T. Valko" wrote:

Sheet 1:
ColumnA ColumnB
8765 -

Is that "dash" entered in the cell or does it represent an empty cell?

Sheet 2:
ColumnA ColumnB
8765 ?

So, what result should appear on Sheet2 for 8765?

--
Biff
Microsoft Excel MVP


"PaulQ" wrote in message
...
Can someone please help me find a formula (or two) for this example. If
you
can show me a couple of ways to do this (so I can learn), I'd greatly
appreciate it! Thanks!

I'm trying to figure out a formula (or two) that will help me
auto-populate
the ? fields within ColumnB of Sheet 2. Also, I want the ? value for
the
"8888" row to return blank, since it does not exist in Sheet 1.

Sheet 1:

ColumnA ColumnB

1234 20
4321 10
5678 11
8765 -
9999 12
7777 13

Sheet 2:

ColumnA ColumnB

5678 ?
8765 ?
1234 ?
4321 ?
8888 ?


.