Using an If statement with the Match Function

[Dave Lucas]

I want to use an if statement described below
(IF(MATCH(U5,$AJ5:$BB5,0)>0,U5,if(another statement)) where if the cell u5 is in the listed range then the value of the cell is u5 if it is not then nest another if statement. The problem if the first match does not work it returns an "#N/A" and the second part of the if statement does not kick in. How do I use a qualifier that will recognize the "#N/A" so that the second part of the if statement works?

Thanks

Dave

 

[IML]

Use the formulation of
IF(isnumber(MATCH(U5,$AJ5:$BB5,0)),

 

[Dave Lucas]

You guys are good. Works great.

Thanks
Dave

 

[4444bv]

IML,
Could you help me understand what the "isnumber" function does in the above statement. I had somewhat of the same issue as Dave and used the isnumber function to make it work after reading this thread. Thank you for helping me solve my issue btw, I just need a better understanding how this works.

Thank you,
Brian

 

[Jo1234la]

IML,
Could you help me understand what the "isnumber" function does in the above statement. I had somewhat of the same issue as Dave and used the isnumber function to make it work after reading this thread. Thank you for helping me solve my issue btw, I just need a better understanding how this works.

Thank you,
Brian

Something similar, can someone help?

I want to compare and match two values in two separate columns and return a value from that same row. Example:

D1= If A1 matches B1 then return value C1

I am working in numbers (iWork) I assume this formula will work the same or would like to try but I need the proper syntax for this can anyone help? Thanks in advance!

 

[deckerch]

I want to create a status rollup in the main task based on project status entries in the subtasks. I'm trying to use the formula below, but am getting the error message "You've entered too many arguments for this function".

=IF((istext(MATCH("Need Help",$F3:$F7),"Need Help",(IF(MATCH("Some Issues",$F3:$F7),"Some Issues","Not Found")))))

I also tried the formula below, but get #N/A when the search area only contains "Some Issues".

=IF(MATCH("Need Help",$F3:$F7),"Need Help",IF(MATCH("Some Issues",$F3:$F7),"Some Issues","Not Found"))

Any suggestions??

Thank you!

 

[Kuljack]

Something similar, can someone help?

I want to compare and match two values in two separate columns and return a value from that same row. Example:

D1= If A1 matches B1 then return value C1

I am working in numbers (iWork) I assume this formula will work the same or would like to try but I need the proper syntax for this can anyone help? Thanks in advance!

FYI, if you have a new problem it's best etiquette to start a new forum.

isnumber is a "Boolean" function in that it references the data you're obtaining, and responds TRUE if it is a number, and FALSE if it is anything else.

=isnumber(A1)

if A1 = 1, the cell will return TRUE

if A1 = One, the cell will return FALSE