Dale Fye
asked on
Vlookup value in different worksheet
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try adding a zero by copy-pastespecial-add
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),"no t 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
=IFERROR(VLOOKUP(B3,'Gas 092013'!$B:$J,9,FALSE),"no
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
If that was what you were looking for, why close the question? Accept my suuggestion as your solution.
ASKER
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.
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
Alternatively, share the 500 points with myself and Barry who also provided a solution on the vlookup issue.
Thanks
Rob H
Maybe it is because you want the 9th column of the data not the 10th
Open in new window
Regards