If/VLookup/Match Formula Results Inconsistent

Hey folks,

This is my first thread, so hopefully I can explain this well enough.

I have two columns of information in two tabs (see attached). The second column, "Cutover Phase", in the second tab is where this formula is fighting me.

The formula is:
=IF(ISNA(VLOOKUP(A3,CHECKLIST,MATCH($B$2,'Reference Tab'!$2:$2,FALSE))),"NOPE!",(VLOOKUP(A3,CHECKLIST,MATCH($B$2,'Reference Tab'!$2:$2,FALSE),FALSE)))

The VLookup references the instrument tag and finds in it the "Checklist" list on the first tab. I needed to add the Match function incase a user wanted to add a column between the "A" and "B" column (The attached example is just a small piece of a much bigger spreadsheet). I also have an ISNA function so it stands out incase the tag is deleted (Right now it says "NOPE!" until I can figure this out.

Even though it's the same formula is used, for instrument tags that start with "AT", it results in a NOPE! instead of the cutover phase number. And after some playing around, I noticed if I put in the letters a thru h, it doesn't work. But if I put the letter i and up in, it works!!

Why does it care what letter is in there? Any suggestions or recommendations would be greatly appreciated!

Hi hardpenguin,
Welcome to the forum
With Vlookup "The values in the first column of table_array must be placed in ascending sort order" Excel Help.

Your first VLOOKUP does not have the FALSE 4th argument to determine exact match:

=IF(ISNA(VLOOKUP(A3,CHECKLIST,MATCH($B$2,'Reference Tab'!$2:$2,FALSE),FALSE)),"NOPE!",(VLOOKUP(A3,CHECKLIST,MATCH($B$2,'Reference Tab'!$2:$2,FALSE),FALSE)))

Hey hardpenguin
try combining isnumber/match/index

=IF(ISNUMBER(MATCH('Formula Tab'!A3,'Reference Tab'!A1:A8,0)),INDEX('Reference Tab'!B1:B8,MATCH('Formula Tab'!A3,'Reference Tab'!A1:A8,0)),"Nope")