I need to lookup a column on one sheet and reference a column and value on another sheet.

@Paul Newcome THANK YOU! It worked. I appreciate you! One more question. I know, I know. Is there a way to change the message that returns? Instead of NO MATCH I would like to say Not Complete or should I just do conditional formatting? I'm asking because these statuses will be viewed by executives and I don't want to keep explaining what no match means.

@Robin Hannah

Insert your INDEX MATCH within an IFERROR function:

=IFERROR(INDEX({Check List Range 1}, MATCH([Offline Model]@row, {Check List Range 2}, 0)), "Not Complete")

Hope it helped,

Thanks for the hand @Paul Newcome

@David Joyeuse Unfortunately, your formual returned all results as Not Complete even the ones that displayed a status of "Complete" previously. I hate to be a pain.

So I entered this formula instead and it worked. Thank You!

=IFERROR(INDEX({Check List Range 1}, MATCH([Offline Model]@row, {Developer Check List Range 2}, 0)), "Not Complete")

You will want to wrap your formula in an IFERROR.

=IFERROR(original_formula, "Not Complete")

All comments

Hi @Robin Hannah

=INDEX({Model Status Range Sheet 2}, MATCH([Model# - Sheet 1]@row, {Model# Range Sheet 2},0))

That should be working.

Hope it helped!

@David Joyeuse Thank you so much for responding. It should be working as it found one match from Sheet 2 but there are three matches, so two of the matches are not recognized. Any thoughts?

My formula using the real sheet names:

=INDEX({Dev Check List Range 1}, MATCH([Offline Model #]@row, {Dev Check List Range 2}, 0))

@Robin Hannah

If the MATCH found a result, then I'd say the problem does not lie in the formula. Maybe there's a difference between what you think is a match? Like a number on one cell that is seen as a string on the other. Or some Caps here and there that the formula doesn't consider as matches?

Could you provide us a screenshot of both sheets? with no sensitive datas or confidential ones on them?

@David Joyeuse Here are screen shots below. I'm not sure if the sort order matters or not but I was unable to sort the Off column in ASC order even though the data element is text/number. I even copied the column and performed a sort (since the original is a primary) and it did not sort.

Also is there a way to state "Not Complete" instead of No Match?

Sheet 1

Sheet 2

How exactly is the data being entered into the Off and Offline columns?

@Paul Newcome For sheet 1 the information is static. I imported the data in and it will not be refreshed. For Sheet 2 The data is the result of a formula in the column adjacent to it that has a long description. The formula copies the first 4 letters or the offline number itself without the text description.

I think that may be the problem. Some are text values whereas some are numerical values. Try inserting a helper column into each sheet and use

=Off@row + ""

and

Offline@row + ""

respectively.

This converts everything into text values. Comparing these two helper columns means you are now comparing like data types.

@Paul Newcome Hi I inserted helper columns as you recommended, now the formula is not yielding any results although there are matches. I don't know what else to do.

Are you using the helper columns in your formula? Can you post a screenshot of the data now along with copy/pasting the exact formulas from the sheets?

Sheet 1

Offline Model column formula =Offline@row + ""

Model Status-1B column formula =INDEX({Check List Range 2}, MATCH([Offline Model]@row, {Check List Range 1}, 0))

Check List Range 2 is the Model column in Sheet 2

Check List Range 1 is the Status column in Sheet 2

Sheet 2

Model_No column formula =LEFT([Model#]1, 4)

Model column formula =[Model_No]@row + ""

Ok. The problem is with your ranges. You have them flipped around. Think of it this way... You want to INDEX (or pull) from the Status column based on a MATCH in the Model column.

As is, you are INDEXing (or pulling) from the Model column based on a MATCH in the Status column.

Try switching your ranges around and see if that clears it up for you.

@Robin Hannah

Insert your INDEX MATCH within an IFERROR function:

=IFERROR(INDEX({Check List Range 1}, MATCH([Offline Model]@row, {Check List Range 2}, 0)), "Not Complete")

Hope it helped,

Thanks for the hand @Paul Newcome

@David Joyeuse Unfortunately, your formual returned all results as Not Complete even the ones that displayed a status of "Complete" previously. I hate to be a pain.

So I entered this formula instead and it worked. Thank You!

=IFERROR(INDEX({Check List Range 1}, MATCH([Offline Model]@row, {Developer Check List Range 2}, 0)), "Not Complete")