Return value

[DebP]

I am working on a sheet with the following:

Sheet 1 Col A = Description
Sheet 1 Col B = ID
Sheet 1 Col C = Returned value from Sheet 2 Col B when value in Sheet 1
Col B matches value in Sheet 2 Col A

I would like to take a cell from Sheet 1 Col B and find the matching
value in Sheet 2 Col A and fill in Sheet 1 Col C with associated value
from Sheet 2 Col B.

I know this is a simple lookup but I can't get it to work.

 

[Biff]

Hi!

Try this:

=IF(COUNTIF(B1,Sheet2!A:A),VLOOKUP(B1,Sheet2!A:B,2,0),"")

Biff

 

[DebP]

Thanks for the help, but I tried your suggestion and nothing happened.

 

[Biff]

Thanks for the help, but I tried your suggestion and nothing happened.

Ok, let's examine what that means!

=IF(COUNTIF(B1,Sheet2!A:A),VLOOKUP(B1,Sheet2!A:B,2,0),"")

I take it that "nothing happened" means the formula returned a blank which
is what the "" is for.

So, if the formula returned a blank that means this portion:

COUNTIF(B1,Sheet2!A:A)

evaluated to zero which means that there is no match of B1 on Sheet2 in
column A.

If you can see for "certain" that there is in fact a match try this:

Find the matching cell on Sheet2 of column A. Assume that cell is A40.

Enter this formula in some empty cell:

=Sheet1!B1=Sheet2!A40

If they are in fact matches that formula will return TRUE and the lookup
formula should have returned the appropriate corresponding value. If the
formula returns FALSE then they are not matches. They may look the same but
can still not be matches. One value may have leading or trailing spaces that
you can't see. One value may be a TEXT number and the other may be a NUMERIC
number.

Biff

 

[Biff]

Ooops!

I committed a major blunder! I'm downright embarrassed!

=IF(COUNTIF(B1,Sheet2!A:A),VLOOKUP(B1,Sheet2!A:B,2,0),"")

I have the arguments backwards in the Countif function!!!!!!! <ouch>

Should be:

=IF(COUNTIF(Sheet2!A:A,B1),VLOOKUP(B1,Sheet2!A:B,2,0),"")

Biff