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

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

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

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

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 -

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 -

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 -

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 -