if/lookup/index/match?

[JR]

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

 

[Pete_UK]

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

 

[JR]

Thank you very much. That's perfect!
--
JR

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

 

[JR]

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]

You're welcome - thanks for feeding back.

Pete

Thank you very much. That's perfect!
--
JR






- Show quoted text -

 

[Pete_UK]

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

 

[JR]

Your right again, the round function works.
thanks
JR

 

[Pete_UK]

Glad to hear it.

Pete

Your right again, the round function works.
thanks
JR






- Show quoted text -