CODEWITHSUNDEEP
excelexcel-formula
AskMe
AskMe

Reputation: 2583

find records from one column in another: excel

I have few numbers like this in Excel:

            Col B       Col I
            ------------------
            104        160
            105        236
            158        342
            160        630
            250
            290

Both are extracted from a DB and inputted in Excel in proper Order (in their respective columns)

I see "160" of Column I is present in Col B

Can any one please give me a formula , where I need to find "the numbers present in col I is present or NOt in Col B" ?

I want result like :

            Col B       ColI     Col C (My result columns)
            -------------------------------
            104        160
            105        236
            158        342
            160        630        160
            250
            290

I have variable number of records on Col B and Column I . There are around 1K records. 

            I have used formula: =INDEX($B$2:$B$989,MATCH($I2,$B$2:$B$989,0))

However, its not helping. May be highlight those in a color will help as well.Please help.

Upvotes: 0

Views: 124

Answers (2)

G42
G42

Reputation: 10019

You could use a vlookup.

=IFNA(VLOOKUP(A1,B:B,1,FALSE),"")

=IFNA(VLOOKUP(A1,B:B,1,FALSE),"")

Upvotes: 0

Egan Wolf
Egan Wolf

Reputation: 3573

Your formula looks good, however, to achieve the result you included, you should use it the other way around. You can also add IFERROR() to get rid of all that errors and leave blank cells. Check this:

=IFERROR(INDEX($I$2:$I$20,MATCH($B2,$I$2:$I$20,0)),"")

Change number of rows for data in column I.

Upvotes: 1

Related Questions