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

Robin Hannah

I have a Model# on sheet 1. I need to search for the same Model# on sheet 2 and if found, look at that Model's status and return that value to sheet 1. Caveat: the Model Status on sheet 2 is a drop down list (Complete, Not Complete), so I need whatever value is selected to pull into sheet 1.

I tried a VLOOKUP using the formula below. It returned a NO MATCH even though the value is there. I also tried an INDEX with a VLOOKUP and no luck.

=VLOOKUP([Model #]2, {Check List Range 2}, 2, false)

Robin Hannah

@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.

David Joyeuse

@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

Robin Hannah

@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")

Paul Newcome

You will want to wrap your formula in an IFERROR.

=IFERROR(original_formula, "Not Complete")

David Joyeuse

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!

Robin Hannah

@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))

David Joyeuse

@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?

Robin Hannah

@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

Paul Newcome

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

Robin Hannah

@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.

Paul Newcome

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.

Robin Hannah

@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.

Paul Newcome

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?

Robin Hannah

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 + ""

Paul Newcome

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

@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.

David Joyeuse

@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

Robin Hannah

@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")

Paul Newcome

You will want to wrap your formula in an IFERROR.

=IFERROR(original_formula, "Not Complete")