• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Facing challenges in using vlookup Function.

karthick87

karthick87

Member
Dear All,

Greetings to everyone!

First of all my heartiest thanks to all the forum members who give their continuous support. I'm just a beginner started learning Excel. I want to achieve the following,

I have two columns in which both column contains some results. Column 1 has 8000 cell values and Column2 contains 7250 values. I know these 7250 values are already in Column1, I just want to find out the missing 750 values comparing both the columns.

Note: I would like to have the missing resulting value in third column. I've just attached an sample excel file with this message which contains sample values. Can anybody help me to figure out where I am going wrong. Thanks in advance!
 

Attachments

Hi Karthick,

You can try this also..

=IFERROR(INDEX(Group1,SMALL(IF(COUNTIF(Group2,Group1)=0,ROW(Group1)-1),ROW(A1))),"")

Confirm the formula by using Ctrl + Shift + Enter, Not just Enter
 

Attachments

First, the answer to your question.
You could use this formula, entered as an array using Ctrl+Shift+Enter,:
=IF(COUNTA(B:B)+ROWS(C$2:C2)>COUNTA(A:A),"",INDEX(A:A,SMALL(IF(COUNTIF($B$2:$B$25,$A$2:$A$25)=0,ROW($A$2:$A$25)),ROWS(C$2:C2))))

Copy down as far as you think would be needed.

WARNING:
With 8000 records to look at, this could slow things down. If you don't really need a "live" result, I'd create a helper column next to col A with formula:
=ISNA(MATCH(A2,B$2:B$8000,0))
and then filter that column on "TRUE" to see all the missing values. A few more steps, but much less calculation intensive.
 
Thank you very much for all the replies. Hi Luke, the first formula is not working the second one does what I want. But cant we use vlookup for this type of jobs??
 
Possibly...but it would be the same princicple, just trying to catch an error. Something like:
=ISNA(VLOOKUP(....))

Just realized that formula could also be:
=COUNTIF(B:B,A2)=0
Which is probably a little more efficient that the MATCH function I gave before.
 
Can you please tell me what it does?? >> "Ctrl + Shift + Enter"

Why do we need to use that after entering the formula?
 
You must log in or register to reply here.
Back
Top