Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if/lookup/index/match?
I want to find/search a specific number in a row of values then return that
value if found to a given cell or otherwise return the value of 0. This may seem a simple question but I can't seem to find the correct worksheet function. thanks JR |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if/lookup/index/match?
It would help if you gave details of the cell you are trying to look
up (assume this is A1), and the range of cells you are trying to find a match in (assume this is Sheet2!A6:Z6), but with my assumptions you could try this: =IF(ISNA(MATCH(A1,Sheet2!$A$6:$Z$6,0)),0,A1) Obviously, change the cell and range references to suit your data. Hope this helps. Pete On Nov 24, 12:54*pm, JR wrote: I want to find/search a specific number in a row of values then return that value if found to a given cell or otherwise return the value of 0. This may seem a simple question but I can't seem to find the correct worksheet function. thanks JR |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if/lookup/index/match?
Thank you very much. That's perfect!
-- JR "Pete_UK" wrote: It would help if you gave details of the cell you are trying to look up (assume this is A1), and the range of cells you are trying to find a match in (assume this is Sheet2!A6:Z6), but with my assumptions you could try this: =IF(ISNA(MATCH(A1,Sheet2!$A$6:$Z$6,0)),0,A1) Obviously, change the cell and range references to suit your data. Hope this helps. Pete On Nov 24, 12:54 pm, JR wrote: I want to find/search a specific number in a row of values then return that value if found to a given cell or otherwise return the value of 0. This may seem a simple question but I can't seem to find the correct worksheet function. thanks JR |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if/lookup/index/match?
Back Again - I tried your formula in a new spreadsheet to see how it worked,
and it worked great, but when I inserted it into my actual spreadsheet, it's not working. The range of cells/data the formula refers to are from cells that have been calculated. Is this the problem? I may not be clear in my explanation. -- JR "Pete_UK" wrote: It would help if you gave details of the cell you are trying to look up (assume this is A1), and the range of cells you are trying to find a match in (assume this is Sheet2!A6:Z6), but with my assumptions you could try this: =IF(ISNA(MATCH(A1,Sheet2!$A$6:$Z$6,0)),0,A1) Obviously, change the cell and range references to suit your data. Hope this helps. Pete On Nov 24, 12:54 pm, JR wrote: I want to find/search a specific number in a row of values then return that value if found to a given cell or otherwise return the value of 0. This may seem a simple question but I can't seem to find the correct worksheet function. thanks JR |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if/lookup/index/match?
You're welcome - thanks for feeding back.
Pete On Nov 24, 1:28*pm, JR wrote: Thank you very much. That's perfect! -- JR "Pete_UK" wrote: It would help if you gave details of the cell you are trying to look up (assume this is A1), and the range of cells you are trying to find a match in (assume this is Sheet2!A6:Z6), but with my assumptions you could try this: =IF(ISNA(MATCH(A1,Sheet2!$A$6:$Z$6,0)),0,A1) Obviously, change the cell and range references to suit your data. Hope this helps. Pete On Nov 24, 12:54 pm, JR wrote: I want to find/search a specific number in a row of values then return that value if found to a given cell or otherwise return the value of 0. This may seem a simple question but I can't seem to find the correct worksheet function. thanks JR- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if/lookup/index/match?
The MATCH function is looking for an exact match, so if A1 is
calculated or cells in the lookup range, then it is possible for numbers to look like they should match but they don't. You could use the ROUND function to ensure that your calculations are returned with consistent numbers of decimals. Hope this helps. Pete On Nov 24, 2:00*pm, JR wrote: Back Again - I tried your formula in a new spreadsheet to see how it worked, and it worked great, but when I inserted it into my actual spreadsheet, it's not working. The range of cells/data the formula refers to are from cells that have been calculated. Is this the problem? I may not be clear in my explanation. * -- JR "Pete_UK" wrote: It would help if you gave details of the cell you are trying to look up (assume this is A1), and the range of cells you are trying to find a match in (assume this is Sheet2!A6:Z6), but with my assumptions you could try this: =IF(ISNA(MATCH(A1,Sheet2!$A$6:$Z$6,0)),0,A1) Obviously, change the cell and range references to suit your data. Hope this helps. Pete On Nov 24, 12:54 pm, JR wrote: I want to find/search a specific number in a row of values then return that value if found to a given cell or otherwise return the value of 0. This may seem a simple question but I can't seem to find the correct worksheet function. thanks JR- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if/lookup/index/match?
Your right again, the round function works.
thanks JR "Pete_UK" wrote: The MATCH function is looking for an exact match, so if A1 is calculated or cells in the lookup range, then it is possible for numbers to look like they should match but they don't. You could use the ROUND function to ensure that your calculations are returned with consistent numbers of decimals. Hope this helps. Pete On Nov 24, 2:00 pm, JR wrote: Back Again - I tried your formula in a new spreadsheet to see how it worked, and it worked great, but when I inserted it into my actual spreadsheet, it's not working. The range of cells/data the formula refers to are from cells that have been calculated. Is this the problem? I may not be clear in my explanation. -- JR "Pete_UK" wrote: It would help if you gave details of the cell you are trying to look up (assume this is A1), and the range of cells you are trying to find a match in (assume this is Sheet2!A6:Z6), but with my assumptions you could try this: =IF(ISNA(MATCH(A1,Sheet2!$A$6:$Z$6,0)),0,A1) Obviously, change the cell and range references to suit your data. Hope this helps. Pete On Nov 24, 12:54 pm, JR wrote: I want to find/search a specific number in a row of values then return that value if found to a given cell or otherwise return the value of 0. This may seem a simple question but I can't seem to find the correct worksheet function. thanks JR- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if/lookup/index/match?
Glad to hear it.
Pete On Nov 25, 3:36*am, JR wrote: Your right again, the round function works. thanks JR "Pete_UK" wrote: The MATCH function is looking for an exact match, so if A1 is calculated or cells in the lookup range, then it is possible for numbers to look like they should match but they don't. You could use the ROUND function to ensure that your calculations are returned with consistent numbers of decimals. Hope this helps. Pete On Nov 24, 2:00 pm, JR wrote: Back Again - I tried your formula in a new spreadsheet to see how it worked, and it worked great, but when I inserted it into my actual spreadsheet, it's not working. The range of cells/data the formula refers to are from cells that have been calculated. Is this the problem? I may not be clear in my explanation. * -- JR "Pete_UK" wrote: It would help if you gave details of the cell you are trying to look up (assume this is A1), and the range of cells you are trying to find a match in (assume this is Sheet2!A6:Z6), but with my assumptions you could try this: =IF(ISNA(MATCH(A1,Sheet2!$A$6:$Z$6,0)),0,A1) Obviously, change the cell and range references to suit your data. Hope this helps. Pete On Nov 24, 12:54 pm, JR wrote: I want to find/search a specific number in a row of values then return that value if found to a given cell or otherwise return the value of 0.. This may seem a simple question but I can't seem to find the correct worksheet function. thanks JR- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Index Match?? | Excel Discussion (Misc queries) | |||
Not sure what to use? Lookup / Index / Match etc | Excel Discussion (Misc queries) | |||
index/match/lookup??? | Excel Discussion (Misc queries) | |||
index match lookup | New Users to Excel | |||
index / match /lookup ? help | Excel Worksheet Functions |