I have a code that is =IF(VLOOKUP($U7335,'2022_Contracts.xlsx'!$T:$T, MATCH($E7335,'2022_Contracts.xlsx'!$F:$F,0),FALSE), "Match", "No Match")

What it is doing currently is matching the number in E7335 to the correct row but then when it does the VLOOKUP for that row it gives me "#REF!". Also is there a way to make the vlookup only look for U7335 in the row that the MATCH gives because currently, it is finding the same number in U7335 else where in the table and saying it is correct. Any help would be greatly appreciated.

CodePudding user response：

Use:

=IF(ISNUMBER(MATCH($U7335,INDEX('2022_Contracts.xlsx'!$F:$ZZ,MATCH($E7335,'2022_Contracts.xlsx'!$F:$F,0),0),0)),"MATCH","NO MATCH")

The third criterion of VLOOKUP is the column number in which the return is. Your range in 2nd criterion is only one column and therefor if the match is in any row but the first one it will error.

Now if we only care about column T and not the full row:

=IF(VLOOKUP($E7335,'2022_Contracts.xlsx'!$F:$T,15,FALSE)=$U7335,"MATCH","NO MATCH")

Now the range is the all of F through T and we return the value that is in T where F is E7335 and check if it equals u7335.