"If Match" formula

Scotty Andersen

Need help with an "If Match" formula to search for if a cell at row is on a list and return a check to another column

Kaveri Vipat

Hi Scotty Andersen,

You can try this formula as per your condition:

The list column is used as a reference to check whether the color column's cell falls within that range. If it does, the box will be checked; otherwise, it will remain unchecked.

Hope it will work for you.

Jason Albrecht

Hi @Scotty Andersen

Something like this in the check column then?

=IF ( INDEX ( list range , MATCH ( cell@row , list range , 0 ), list check column number ) = cell@row , 1 , 0 )

Scotty Andersen

I tried so I have a list of numbers that is my row has that number i need the box to be checked

Jason Albrecht

Sorry @Scotty Andersen,

I must be missing something. May I ask for your last comment to be rephrased? I'm not sure if you're making a comment or asking a question, or both!?

If you'd like some more help, please provide a screenshot of your work (redacting sensitive information), so we can better understand what you're after.

Scotty Andersen

here is the formula so the list of poles on the list are on a separate sheet. =IF(MATCH([MPU Pole #]@row, {Sheet- Manitowoc Rejected Poles Range 1}), 1, 0)

Jason Albrecht

Hello @Scotty Andersen

Did you end up finding a solution?

Looking at your formula I note that the MATCH function "Returns the relative position of a value in a range (lookup table). The first position is 1." The function also has an optional search_type, where I assume the data in the other sheet is not sorted?

So, may I recommend the following?

=IFERROR(IF(MATCH([MPU Pole #]@row, {Sheet- Manitowoc Rejected Poles Range 1}, 0) > 0, 1, 0), 0)

The formula says:

• If there is a match (where the position number returned is greater than 0), return "1"
• If there is no match, or if there is an error, return "0"

nkrasow1

I keep getting a return for blanks. Any suggestions?