Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Find and Populate in Excel

Posted on 2016-07-19
10
36 Views
Last Modified: 2016-08-15
Isn't there a way I can look up values in one spreadsheet to populate values in another spreadsheet?

I want to populate the areas associated with owners on the FORMATTED DATA tab.  The owner area information is located on the RAW DATA tab.

Thank you,
Find-n-Replace.xlsx
0
Comment
Question by:Dee
10 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 41719152
Hi,

pls try

=VLOOKUP(D2,'Raw data'!C:D,2,0)

Open in new window

Regards
Find-n-Replace.xlsx
0
 

Author Comment

by:Dee
ID: 41719198
Thank you!  It works fine on the sample file but fails on an actual data set.  (data set attached).

Any ideas?
Find-n-Replace---Data-set.xlsx
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 41719220
It is because the looked up numbers are not available in the raw data
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:Dee
ID: 41719239
???

The very first number (105049276) is on row 295 in the raw data tab....

Unless I am missing something else....
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41719261
The values on Raw Data tab are text strings that look like numbers, presumably downloaded from some other system that has exported as text.

Try this instead:
=VLOOKUP(TEXT(C2,"0"),Raw!A:B,2,0)

I notice on your Formatted sheet the values in column C are repeated several times, the one quoted above all the way down to row 137. Putting the lookup above in column B will repeat the same value retrieved from the lookup. Are you expecting different values?
0
 

Author Comment

by:Dee
ID: 41719386
AH!!!!!!  Yes, that must have been it.  The text conversion works fine!  Yes we are expecting the same value multiple times throughout the formatted results.

Thank you again for the nice solution.
0
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 41719415
You could also use

=VLOOKUP(""&C2,Raw!A:B,2,0)

Open in new window

Regards
0
 

Author Comment

by:Dee
ID: 41719444
Thank you Rgonzo1971!
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question