If, Lookup, Match or a combination of all
 

Hi

I have 1 excel spreadsheet with several worksheets. What I need to do is to
take the value from 1 cell in one sheet look at a list of values on a second
sheet and return the value but only if there is an exact match else I need it
to return a 0. At the moment with Lookup it is returning a value that is the
closest match.

Sheet 1
Column A Column B Column C Column D Column J
sumtotal name title Code Value of Col B
from sheet 2

Sheet 2
Column A Column B
Code Value

I need it to look the value from column D in Sheet 1 and match it to the
Code in Column A on sheet 2 and return the value of Column B to Col J in
sheet 1 else return a 0

Thanks

If, Lookup, Match or a combination of all
 

On Tue, 22 Jul 2008 03:14:38 -0700, Beverly Darvill
wrote:

Hi

I have 1 excel spreadsheet with several worksheets. What I need to do is to
take the value from 1 cell in one sheet look at a list of values on a second
sheet and return the value but only if there is an exact match else I need it
to return a 0. At the moment with Lookup it is returning a value that is the
closest match.

Sheet 1
Column A Column B Column C Column D Column J
sumtotal name title Code Value of Col B
from sheet 2

Sheet 2
Column A Column B
Code Value

I need it to look the value from column D in Sheet 1 and match it to the
Code in Column A on sheet 2 and return the value of Column B to Col J in
sheet 1 else return a 0

Thanks


Try the following formula in Sheet1 cell J2:

=IF(ISERROR(VLOOKUP(D2,Sheet2!$A$1:$B$100,2,FALSE) ),0,VLOOKUP(D2,Sheet2!$A$1:$B$100))

The 100 might have to be increased if you have more than 100 rows of
data in Sheet2

Hope this helps / Lars-Åke

If, Lookup, Match or a combination of all
 

=IF(NOT(ISNA(VLOOKUP(value,Sheet2!Lookup_Table,2,F ALSE))),VLOOKUP(value,Sheet2!Lookup_Table,2,FALSE) ,"")

--
__________________________________
HTH

Bob

"Beverly Darvill" wrote in message
...
Hi

I have 1 excel spreadsheet with several worksheets. What I need to do is
to
take the value from 1 cell in one sheet look at a list of values on a
second
sheet and return the value but only if there is an exact match else I need
it
to return a 0. At the moment with Lookup it is returning a value that is
the
closest match.

Sheet 1
Column A Column B Column C Column D Column J
sumtotal name title Code Value of Col B
from sheet 2

Sheet 2
Column A Column B
Code Value

I need it to look the value from column D in Sheet 1 and match it to the
Code in Column A on sheet 2 and return the value of Column B to Col J in
sheet 1 else return a 0

Thanks

If, Lookup, Match or a combination of all
 

On Tue, 22 Jul 2008 10:24:52 GMT, Lars-Åke Aspelin
wrote:

On Tue, 22 Jul 2008 03:14:38 -0700, Beverly Darvill
wrote:

Hi

I have 1 excel spreadsheet with several worksheets. What I need to do is to
take the value from 1 cell in one sheet look at a list of values on a second
sheet and return the value but only if there is an exact match else I need it
to return a 0. At the moment with Lookup it is returning a value that is the
closest match.

Sheet 1
Column A Column B Column C Column D Column J
sumtotal name title Code Value of Col B
from sheet 2

Sheet 2
Column A Column B
Code Value

I need it to look the value from column D in Sheet 1 and match it to the
Code in Column A on sheet 2 and return the value of Column B to Col J in
sheet 1 else return a 0

Thanks


Try the following formula in Sheet1 cell J2:

=IF(ISERROR(VLOOKUP(D2,Sheet2!$A$1:$B$100,2,FALSE )),0,VLOOKUP(D2,Sheet2!$A$1:$B$100))

The 100 might have to be increased if you have more than 100 rows of
data in Sheet2

Hope this helps / Lars-Åke


I forgot to explain that it is the FALSE that tells VLOOKUP to look
for an exact value.