Return Corresponding Value Based on Comparing Two Sheets of Data

[PaulQ]

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 ?

 

[T. Valko]

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?

 

[Fred Smith]

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

Regards,
Fred

 

[PaulQ]

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

 

[PaulQ]

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]

You're welcome. Thanks for the feedback.

Regards,
Fred

 

[T. Valko]

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(A10,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,MATCH(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,MATCH(A10,A$2:A$7,0)),"")

=IF(COUNT(MATCH(A10,A$2:A$7,0)),INDEX(B$2:B$7,MATCH(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,MATCH(A10,A$2:A$7,0)-1,0))

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

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

I might have missed a few!