Hi,
I have the formula for the attached worksheet as follows:
=IF(ISNUMBER(MATCH(A1,SHEET2!A:A,0)),"YES","NO")
however it does not give me the correct answer
like for instance in row 22,44, and 71 which is supposed to be Yes instead of No
Please advise,
Thank you
There are trailing spaces on sheet 2, so no match. Try this:
=IF(ISNUMBER(MATCH(A1,TRIM(Sheet2!A:A),0)),"YES","NO")
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Another way, with fewer function calls:
=IF(COUNTIF(Sheet2!A:A,A1&"*"),"Yes","No")
Hope this helps.
Pete
Thank you,
It looks like it only works with the lookup value starting with the letter,
it does not work for the number
for instance:
under sheet 1: 53732
under sheet 2: 53732
with the formula given it still responds as no
Please advise,
Thank you,
If you paste AliG's or Pete's formula into B1 and copy down, the values in B22, B44, B71 change to Yes.
Guessing you have pasted the suggestion into either of the above cells, which references A1
In sheet 2 all numbers are stored as texts.(aligned on the left)
If you type the number in sheet 1 it is stored as number (aligned on the right)
and it is not recognized because on sheet 2 it is a text
You may thy this in Ali's formula.
Formula:
Please Login or Register to view this content.
Thank you,
It works...however I have thousands of rows and took lots of time to recalculate every time I add in a cell.
Is there a way to modify the formula to allow faster recalculation.
Thanks,
Which formula did you use?
Once you enter the formula into cell B1, you can quickly copy it down the column by dragging it down, or by double-clicking the copy icon (the bottom right corner of the cursor).
Hope this helps.
Pete
Humm, I don't see this formula being the problem.
Using formulas only addresses the surface, and your original data remains unchanged.
To address the root cause, you need to modify your data.
Your data has two issues:
Many cells have trailing spaces.
Many values are stored as text.
The quickest way is as follows:
Step 1: Use Find & Replace to replace " " with "":
Find What: (input 1 space)
Replace with: Blank
Step 2: Use Add Value to add 0 to number cells to convert text to value.
Copy any empty cell.
Select column A.
Paste special: Operation: Add
Quang PT
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks