How to look info up on another spreadsheet file in Excel

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
jrh70Asked:
Who is Participating?
 
FernandoFernandesConnect With a Mentor Commented:
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
 
KnutsonBMCommented:
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
 
jrh70Author Commented:
is there a way to insert a variable while doing a vlookup?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
KnutsonBMConnect With a Mentor Commented:
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
 
KnutsonBMConnect With a Mentor Commented:
if you post an example of what you are working with it may make this easier to answer for you...

-brandon
0
 
FernandoFernandesConnect With a Mentor Commented:
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
 
Rob HensonFinance AnalystCommented:
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
 
jrh70Author Commented:
yes that was a typeo.  Sorry
0
 
KnutsonBMCommented:
did any of these solutions work for you?  
0
 
jrh70Author Commented:
I got it to work with modified code of the above.
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.