Solved

Vlookup value in different worksheet

Posted on 2013-11-03
12
498 Views
Last Modified: 2013-11-10
I'm a database geek, but my Excel experience is extremely limited and I cannot get the syntax working properly on a VLookUp() function call.  It might have something to do with a space in a worksheet name of the first worksheet.

I have two worksheets in a workbook. The first is titled 'Gas 092013', the other is 'RAD_Gas_0913'.

I have inserted a column (P) into RAD_GAS_0913 and want to pull in the value from column J in 'Gas 092013' where the value in Column B matches column B in 'Rad_Gas_0913'.

I tried:

=IF(ISNA(VLOOKUP(B3,'Gas 092013'!$B:$J,10,FALSE)), "not found", VLOOKUP(B3,'Gas 092013'!$B:$J,10,FALSE))

If it makes a difference, the "data" in 'RAD_Gas_0913' starts on row 2 of the spreadsheet, but in 'Gas 092013', the data starts on row 9 and ends on row 605.
0
Comment
Question by:Dale Fye (Access MVP)
[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
  • 3
  • 3
  • 2
  • +2
12 Comments
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 39620250
Hi,

Maybe it is because you want the 9th column of the data not the  10th

=IF(ISNA(VLOOKUP(B3,'Gas 092013'!$B:$J,9,FALSE)), "not found", VLOOKUP(B3,'Gas 092013'!$B:$J,9,FALSE))

Open in new window

Regards
0
 
LVL 48

Accepted Solution

by:
Dale Fye (Access MVP) earned 0 total points
ID: 39620270
Nah, that's not it, I already fixed that.

However, I realized that column B on the 'RAD_Gas_0913' is a number formatted as text, and should be a number, which is probably the problem.  So, how do I go about changing that that entire column to numbers, not text?

Using Value(B2) works, but I would prefer to just change that column from text to numeric.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39620290
Try adding a zero by copy-pastespecial-add
0
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!

 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 39620293
one way is to enter in a free cell a 1 and then copy it and use Paste Special Values Operation Multiply

PS
Regards
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39620305
In Excel 2007 you can use IFERROR function to avoid repeating the VLOOKUP in your formula, i.e.

=IFERROR(VLOOKUP(B3,'Gas 092013'!$B:$J,9,FALSE),"not found")

To convert a text-formatted number column to numbers try using "Text to columns" functionality.

Select your column of data > On Data tab select "Text to columns" > Finish

Now your VLOOKUP should work

regards, barry
0
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 250 total points
ID: 39624015
If you have Error Reporting enabled, the cells with numbers as text will have a green triangle in the top left hand corner. When you click on the cell a drop down will appear to the left which when clicked will show an option to conevrt to number.

If you highlight the whole range and select this on the first selected cell it will convert all of them.

Seems quicker than having to do the Text to Columns or copy and pasting.

Thanks
Rob H
0
 
LVL 48

Author Comment

by:Dale Fye (Access MVP)
ID: 39624119
I've requested that this question be closed as follows:

Accepted answer: 0 points for fyed's comment #a39620270
Assisted answer: 250 points for robhenson's comment #a39624015

for the following reason:

Thanks, Rob.  That was what I was looking for for the conversion.  I had over 500 rows and was trying to figure out how to do it all at once.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39624120
If that was what you were looking for, why close the question? Accept my suuggestion as your solution.
0
 
LVL 48

Author Comment

by:Dale Fye (Access MVP)
ID: 39624299
Well, Rob, my question was about getting the VLOOKUP to work, and I achieved that myself.  I could have just closed the post.  However, your post was helpful for future use, so I awarded you 250 points.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39624413
Apologies, hadn't spotted the split of points. A moderator will have to get involved to reduce the points to be allocated for the solution.

Alternatively, share the 500 points with myself and Barry who also provided a solution on the vlookup issue.

Thanks
Rob H
0

Featured Post

Industry Leaders: 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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

751 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