I need to lookup a column on one sheet and reference a column and value on another sheet.
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)
Best Answers

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

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

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

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

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

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