Forum Discussion
Solved
Excel formula for ISNUMBER and VLOOKUP returning wrong value
Hello awesome Excel users.
I have been using ChatGPT to create a formula and I can't seem to get it quite right. To begin with, here is an example of test data I am working with in columns A B and C:
| Column A | MATCH? | Column C |
| Apple | Pear | |
| Pear | Grape | |
| Grape | Apricot |
In the MATCH? column I would like it to display "Yes" if the value in column A has a match in column C. If it does not find an exact match for the value that is in column A, it should display "No".
Here is my formula: =IF(ISNUMBER(VLOOKUP(A2,C:G,1,FALSE)),"No","Yes")
For some reason it is giving me "Yes" for all 3 rows, even though Apple does not exist in column C.
I have asked ChatGPT for advice - it suggested making sure the data formats are the same (both are General, I have also tried Text). Check case sensitivity, and check for unwanted spaces.
Would anyone here have any other suggestions of what I should try?
Thank you!
VLOOKUP() does not return a number for the criteria in column A.
So ISNUMBER() will always be FALSE.
=IF(COUNTIFS($C$2:$C$4,A2),"Yes","No")
VLOOKUP() does not return a number for the criteria in column A.
So ISNUMBER() will always be FALSE.
=IF(COUNTIFS($C$2:$C$4,A2),"Yes","No")- That worked brilliantly, thank you!
