Solved

How to look info up on another spreadsheet file in Excel

Posted on 2011-03-24
10
207 Views
Last Modified: 2013-11-05
I need to do a price lookup from spreadsheet1 to spreadsheat2.  I need to do this via a macro because I have multiple look ups to do.  Can anyone help?


Spreadsheet1

Product A          $1.99
Product B          $2.09
Product C          $0.99

Spreadsheet2

ProductC
ProductA
ProductB
0
Comment
Question by:jrh70
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 6

Expert Comment

by:KnutsonBM
ID: 35206700
i know you said via macro, but a formula would work very well for this, vlookup is designed especially for this =vlookup(a1,'spreadsheet 1'!A:B,2,false) would do the trick

-brandon
0
 
LVL 6

Accepted Solution

by:
FernandoFernandes earned 250 total points
ID: 35206843
and if you have multiple lookups to do, you can put in the different columns, and change the third parameter, which is the column within the lookup range

=vlookup(a1,'spreadsheet 1'!A:C,3,false)
0
 

Author Comment

by:jrh70
ID: 35207723
is there a way to insert a variable while doing a vlookup?
0
 
LVL 6

Assisted Solution

by:KnutsonBM
KnutsonBM earned 250 total points
ID: 35207762
what kind of variable?  you can use an asterisk and it works well, here are a few examples,

=vlookup("*abcdefg*",'spreadsheet 1'!A:B,2,false)

this will look and see if that text is somewhere in the range

=vlookup("*"&A1&"*",'spreadsheet 1'!A:B,2,false)

this will look to see if text from a cell is somewhere in the range
0
 
LVL 6

Assisted Solution

by:KnutsonBM
KnutsonBM earned 250 total points
ID: 35207777
if you post an example of what you are working with it may make this easier to answer for you...

-brandon
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 6

Assisted Solution

by:FernandoFernandes
FernandoFernandes earned 250 total points
ID: 35207800
you can make dynamic vlookup() by using dynamic range (using ranged name), variable to determine which column you want to return, in this case the variable would be a reference to a cell,
even the range of the vlookup could be variable too, depending on selection in a cell, which could have list validation ...

you have to tell us exactly what you want and to make it quicker and easier, upload your file if possible !

:-)
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 35240615
I notice your lookup value on sheet 2 doesn't have the space in like that on the source document. Is that just a type error in your example or is that the case in the real file?

For the lookup to work properly the lookup values have to match.

Cheers
Rob H
0
 

Author Comment

by:jrh70
ID: 35240635
yes that was a typeo.  Sorry
0
 
LVL 6

Expert Comment

by:KnutsonBM
ID: 35240651
did any of these solutions work for you?  
0
 

Author Closing Comment

by:jrh70
ID: 35240654
I got it to work with modified code of the above.
Thanks
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Excel macro sheet auto run and save to xlsx 5 18
Recurring Excel Timelime for Veeam 2 35
Name Rotation 11 29
TT Master Button 18 16
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
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…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

759 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now