Solved

VLookup, multiple worksheets

Posted on 2012-03-29
16
846 Views
Last Modified: 2012-04-04
Hello.

I would like to grab numbers from column B in sheet1, find them on column B in sheet2, and report back the corresponding value in column D in sheet 2 to column C of sheet1.

Here is what I have been playing with in column C of row 25 on sheet1. Excel tells me there is an error, but I can't find what it is!

One more thing, columns A and C in sheet2 are hidden, so I don't know whether the 4 in my formula should be a 2 or not.

=VLOOKUP($B$25;'Sheet2'!$B$2:$D$40000;4;FALSE)
0
Comment
Question by:Karessa
[X]
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
  • 8
  • 8
16 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37783851
It should be 3.

For the range $B$2:$D$40000 column B is 1, C is 2 and D is 3.
0
 

Author Comment

by:Karessa
ID: 37783888
Thanks very much! I changed it to 3.

Now just need to find where the error is coming from.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37783896
What error are you getting?
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37783906
Does your system use semicolons as an separator or should it be a comma?
0
 

Author Comment

by:Karessa
ID: 37788351
You were right about that - the system required commas.

The error changed with the commas. I get #N/A now if I use:

=VLOOKUP($B$24410,Sheet2!$B$2:$D$40000,3,FALSE)

But if I put in the actual value for B24410, which is 1557, the function works!

So there's something wrong with the $B$24410 part of the function?
0
 

Author Comment

by:Karessa
ID: 37788417
Even when I take out the $ from the B24410 part it still doesn't work.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37788575
You need to make sure that the value in the cell B22410 is "exactly" then same as it looks. I mean extra spaces before, after or within the text.
0
 

Author Comment

by:Karessa
ID: 37788779
If I use the value in the cell (1557) it works. If I use the cell name (B22410) it doesn't.

Unfortunately, I need to find a way to use the cell name so I can copy the formula across cells.
0
 

Author Comment

by:Karessa
ID: 37788791
This is how the formula looks, with the cell being B1 for simplicity and the return being NA:

=VLOOKUP(B1,locationList!$B$2:$D$901,3,FALSE)

If I make it look like this, with the value of B1, it works.

=VLOOKUP(1557,locationList!$B$2:$D$901,3,FALSE)
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37788801
Did you try re-entering the value in the cell B22410?

If it still does not work can you post your file with only this data there so that the problem can be sorted out?
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37788816
Also try this formula

=VLOOKUP(B1&"",locationList!$B$2:$D$901,3,FALSE)
0
 

Author Comment

by:Karessa
ID: 37793598
Here's a modified sample file with the formula in C1 of sheet1.

Thanks so much for your time on this.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37793812
Cannot see the file.
0
 

Author Comment

by:Karessa
ID: 37803567
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 37804305
The problem is that the numbers on sheet1 are text and not numbers whereas those on locationList are numbers. You can check this if you enter a formula like

=cell("Type",b1) for sheet1  and
=cell("Type",b1) for locationList

To correct this you can select cell b1 on sheet1, press F2 and then press Enter.

You can also modify the formula to take care of this

=VLOOKUP(--B1,locationList!$B$2:$D$7,3,FALSE)
0
 

Author Closing Comment

by:Karessa
ID: 37808357
Perfect. Wish I could give you more than 500 points. Thanks!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel compare strings 6 47
NEED LOOK FOR NUM 23 25
multiple unique values in different columns 15 31
Vlookup (Microsoft Excel Programming) 5 10
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

740 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