0

So I have a question about cross referencing. In Column A and B I have the original Data, and I want to cross reference it with Column D and E. Now Column D and E is a for places that have more than 10000 Visitors per month, and I want to find out which of my original data shows up in Column D and E. Im trying to use VLookUp and Index&Match but I can't seem to get it to work. First I want to find if A1 shows up in Column D AND if it does, that Column B must equal Column E as well. What I have right now is =Vlookup(A1,D2:F5000,3,false) Column F is just number 1,for binary purpose, but then I dont know how to make sure that the Column B is ALSO equal Column E. I also tried using offset but I can't get that to work either.

P.S. Some of my data shows up as Gg. "Glass of Museum - Cornell" as oppose to "Cornell Glass of Museum", and if i VLookUp that to find that I wouldn't be able to find it since they dont match, but they're the same thing(another issue). Any help is appreciated!enter image description here

Alex P
  • 12,249
  • 5
  • 51
  • 70
user3784616
  • 1,939
  • 3
  • 15
  • 10
  • One way would be to concatenate columns A and B into a new column (e.g. C) and concatenate columns D and E into a new column (e.g. F). Then just do a `VLOOKUP` on C and F. – Alex P Jun 28 '14 at 17:14
  • Ahh thank you very much I never did thought of that. The other issue, if i do concatenate the two is EG. Glass of Museum Cornell vs Cornell Glass of Museum. VLookup wouldn't match the two(not exact match). Any tips on that? EDIT: let me try the concatenate and see if it works, thanks Mr. Alex – user3784616 Jun 28 '14 at 17:18
  • For `Glass of Museum Cornell` vs `Cornell Glass of Museum` you will need to be consistent. Do a find and replace? And to do something like `=IF(NOT(ISNA(VLOOKUP(C2,$C$2:$C$4,1,0))),1,0)` to get a binary outcome (i.e. 1 for match, 0 for no match) – Alex P Jun 28 '14 at 17:24
  • I had the data pulled from 2 different data bases, which is why there is inconsistency between the two(there are over 50000 data points). So...yeah... The Glass of Museum Cornell vs Cornell Glass of Museum is just one example. There are other ones like EG. Paul & Ann WalkWay as oppose to Walkway - Paul and Ann. =/ A lot of different ones :( – user3784616 Jun 28 '14 at 17:30
  • For the non-exact lookup Microsoft offers a free [fuzzy lookup](http://www.microsoft.com/en-nz/download/details.aspx?id=15011) addin – chris neilsen Jun 29 '14 at 07:05

1 Answers1

1

(I see @Alex P beat me to it. However, I have code, neener neener.)

I don't have Excel on this machine, but something like this should work:

First, fill column F (or some empty column) with this formula (this assumes you start in cell F1 -- you probably have some headers):

=TRIM(D1)&"/"&TRIM(E1)

Then, for your lookup:

=VLOOKUP(TRIM(A1)&"/"&TRIM(B1),F1:F5000,3,FALSE)
Tom Zych
  • 13,329
  • 9
  • 36
  • 53
  • Also about my 2nd issue, any tips on that? Vlookup only looks for EXACT wording, not similar wording ._. – user3784616 Jun 28 '14 at 17:25
  • Thank you very much Mr. Tom. This has worked for me :) I'm trying to figure out how to do the EXACT wording, but may have to resort to manual check (which I do not look forward to) :( – user3784616 Jun 28 '14 at 17:38