CODEWITHSUNDEEP
excelformula
user511792
user511792

Reputation: 519

EXCEL match 2 columns against each other

I have two columns of data, they look something like this:

       A         B         C          D

       1                   2        SOME RECORD
       2                   6        SOME RECORD
       3                   10       SOME RECORD
       4                   
       5
       6
       7
       8
       9
      10

So basically column A is a list of indices, where some of them appear in column C with corresponding records saved in column D. Column B is currently empty, and what I want to do is if say index 2 appears in both column A and column C (they matches), then put the record beside C2 in the cell B2. So essentially I want it to look like this:

       A         B             C          D

       1                       2        SOME RECORD
       2      SOME RECORD      6        SOME RECORD
       3                       10       SOME RECORD
       4                   
       5
       6      SOME RECORD
       7
       8
       9
      10      SOME RECORD

Can someone help please?!! Thanks!!!

UPDATE: I tried this and it doesn't work. The data in column D is calculated using a UDF and is refreshing every 1 second. The VLOOKUP function fails even though I can see the 2 indices are the same!! Is it because of the format of the cell or column? I.e. does vlookup compare data type as well?

Upvotes: 1

Views: 5130

Answers (2)

Peter L.
Peter L.

Reputation: 7304

Assuming your data in A starts from A1 - put in B1 the following and autofill:

=IFERROR(VLOOKUP($A1,$C:$D,2,0),"")

This includes handling of missing values.

Upvotes: 2

Dan
Dan

Reputation: 660

You'll want this:

B1=VLOOKUP(A1, C:D, 2, FALSE)

This will look up the value in column A within the array spanning columns C and D. It will give you the value found in the second column (D). FALSE makes it an exact match, otherwise you might get 2 and 20 matching because hey, they're kind of similar...

Upvotes: 0

Related Questions