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" <[email protected]> wrote in message
news:[email protected]...
>
> 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.
>
>
> --
> DebP
> ------------------------------------------------------------------------
> DebP's Profile:
> http://www.excelforum.com/member.php...o&userid=32273
> View this thread: http://www.excelforum.com/showthread...hreadid=520262
>

[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

"DebP" <[email protected]> wrote in message
news:[email protected]...
>
> Thanks for the help, but I tried your suggestion and nothing happened.
>
>
> --
> DebP
> ------------------------------------------------------------------------
> DebP's Profile:
> http://www.excelforum.com/member.php...o&userid=32273
> View this thread: http://www.excelforum.com/showthread...hreadid=520262
>

[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

"Biff" <[email protected]> wrote in message
news:%[email protected]...
> >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
>
> "DebP" <[email protected]> wrote in
> message news:[email protected]...
>>
>> Thanks for the help, but I tried your suggestion and nothing happened.
>>
>>
>> --
>> DebP
>> ------------------------------------------------------------------------
>> DebP's Profile:
>> http://www.excelforum.com/member.php...o&userid=32273
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=520262
>>
>
>