Compare Values in Different Sheets, then copy corresponding cell if matching (Excel 2013)

1
votes

For Excel 2013, please...

  • FIRST off, I'm looking for a formula solution, not macro or such. Just maybe VLOOKUP, or INDEX... those sorts of simple solutions (Just copy paste on a cell).
  • HERE's the problem: I got two Sheets: Sheet1 contains presentation data, Sheet2 (reference) contains reference data.
  • Sheet2 has data that's not arranged. It's got data I copy-pasted from an ever-changing source.
  • I want to always update Sheet1's data using Sheet2.

Here's what they look like (I'll just mockup superhero names and corresponding values):

I got old/existing data in Sheet1, hence:

Sheet1

Column A           Column B
Daredevil          /me want output here/
Spider-Man         /output/
Cyclops            /output/
Wolverine          /output/
Spider-Woman       /output/
Lockheed           /output/
Warpath            /output/

I got new data in Sheet2, hence:

Sheet2

A   Column B       Column C
     Lockheed      Violet
     The Streak    Red
     Cyclops       Blue
     Warpath       Black
     Wolverine     Yellow
     Daredevil     Red
     Kick-Puncher  Red
     Havok         Violet
  • Now, if Sheet1's Column A match with Sheet2's Column B, I want Sheet1's Column B to display Sheet2's Column C, corresponding to the matching value (same row from any cell, but in this case, a cell in Column C);

  • If there's no match, I want it to display: "whatevs". Below is a sample output I'm aiming for:

    Sheet1

Column A          Column B
Daredevil              Red
Spider-Man           whatevs
Cyclops                Blue
Wolverine            Yellow
Spider-Woman         whatevs
Lockheed             Violet
Warpath               Black

I've wasted two working days at this already :'( After that, I've decided I'll let the experts handle this. I can't!

Thank you

2
excelexcel-formulaexcel-2013

2 Answers

0
votes

Seems a very complicated way to describe a simple VLOOKUP type requirement (and you may have issues if you have NBSPs in your data) but basically I think what you are looking for is: 

 =IFERROR(VLOOKUP(A2,Sheet2!A:B,2,0),"whatevs")

in Sheet1 B2 copied down to suit.

0
votes

I've tested this on your shared file.. apparently, it is somewhat different from the question text :

got two Sheets: Sheet1 contains presentation data, Sheet2 (reference) contains reference data.

So.. what I did is.. rename the sheet : Reference data sheet as 'ref', and Presentation as 'prsntn'

In prsntn!B2 cell put this.. and drag downward.

=IFERROR(INDEX(ref!C:C,MATCH(prsentn!A2,ref!B:B,0)),"whatevs")

done.

Explanation : try crunching this link let me know where you stuck, I'll try my best to assist.

Hope it helps.