CODEWITHSUNDEEP
exceltextmultiple-columns
maliks
maliks

Reputation: 1112

Compare text in Excel columns and then assign corresponding value to specific column

I have some values in Excel worksheet i.e. values in column B, D and E are:

**B**                       **C** **D**       **E**

Cryptography                  11    1         Applied Mathematics
Machine Learning              23    2         Analysis of Algorithms
Networks and Communication    26    3         Automata Theory
Networks and Communication    26    4         Artificial Intelligence
Robotics                      30    5         Biological Networks
Computer Graphics             10    6         Combinatorial Optimization
Data Mining                   12    7         Computability Theory
Combinatorial Optimization    6     8         Computational Complexity Theory
Applied Mathematics           ..    9         Computer Vision
Analysis of Algorithms        ..    10        Computer Graphics
Information Retrieval         ..    11        Cryptography
Artificial Intelligence       ..    12        Data Mining
Artificial Intelligence       ..    13        Data Structures
Programming Languages         ..    14        Databases
Data Mining                   ..    15        Digital Logic
Cryptography                  ..    16        Distributed Computing
Information Retrieval         ..    17        Evolutionary Computation
Machine Learning              ..    18        Human Computer Interaction
Machine Learning              ..    19        Image Processing
...                           ..    ..        ...
...                           ..    ..        ...
...                           ..    ..        ...

whereas column C is empty.

Now I have to match values from column B with column E, the row where the match found, I have to copy the corresponding ID from column D and place it into the column C. Whereas I have put some values manually in column C for a quick idea.

Upvotes: 1

Views: 1745

Answers (1)

BruceWayne
BruceWayne

Reputation: 23283

If I understand correctly, an Index/Match formula (a little more powerful than Vlookup) should work fine:

=INDEX($D$1:$D$19,MATCH($B1,$F$1:$F$19,0)). ENTER and drag down. Note to change the 19 to the last row of your data ranges.

This will return an index, being a cell in range D1:D19. It'll first though look to range F1:F19 for a match of the value of cell B1, and get that row, then use that row in the Index() to return the value.

Edit: You can wrap this in an Iferror() function to output some text if there's an error. I.e. =IfError(INDEX($D$1:$D$19,MATCH($B1,$F$1:$F$19,0)), $B1 & " not found")

Edit2: Why didn't I use vlookup, if they're comprable? Because the way you have your data laid out currently doesn't really allow for it (easily, anyways). Try to set it up yourself and you'll see: =Vlookup(B1,d1:F19,1)...doesn't make sense. You'd need to rearrange your data first, and put the column D numbers in G, then this one would work: =VLOOKUP(B1,F1:G19,2,FALSE)

Upvotes: 1

Related Questions