Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combination...SumProduct, Index, Match? | Excel Worksheet Functions | |||
Lookup valid combination of multiple cells | Excel Worksheet Functions | |||
Lookup? Match? pulling rows from one spreadsheet to match a text f | Excel Worksheet Functions | |||
Lookup or Match ? | Excel Worksheet Functions | |||
combination of vlookup and match question | Excel Worksheet Functions |