LinkBack URL
About LinkBacks
Why am I having this issue doing vLookups??
In Sheet1 column B, I am trying to return the values on Sheet2 Column B. This is a small example of a larger issue I am having on my spreadsheet.
See Attached Book21.xlsx
Why am I having this issue doing vLookups??
In Sheet1 column B, I am trying to return the values on Sheet2 Column B. This is a small example of a larger issue I am having on my spreadsheet.
See Attached Book21.xlsx
the data you posted in your sheet in sheet1 doesn't appear to exist in sheet2, that would be one reason you don't get any back.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
None of the values in Sheet1 match exactly with those in Sheet2, so you are getting #N/A errors. Also, some of the values in column A of Sheet1 are numbers and others are text values that look like numbers, whereas in Sheet2 they are only numbers. (Also, your Calculation Mode is set to Manual).
If you change your formula in B2 of Sheet1 to this:
=VLOOKUP(A2*1,Sheet2!A:B,2)
then you will get the final number in Sheet2, as all your lookup values are larger.
Hope this helps.
Pete
Two more reasons other than what Sambo has mentioned.
1) Numbers in col. A on Sheet1 are formatted as text.
2) Calculation Option is set to Manual. Set it to Automatic. Formulas Tab --> Calculation Options --> Automatic
You may use this formula rather to get rid of formatting issue.
In B2
and copy down.
Please Login or Register to view this content.
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
Is it possible for me to just change the format on both sheets when this happens? Example, Select the entire column, right click, Format cells, and select "General"?
it is difficult to diagnose the reason you are getting "#N/A" in your sheet1 because the sample you posted, the values in sheet1 weren't in sheet2 as I noted.
Any other reasons - we can't tell. If you have some hidden spaces in one and not in the other that wouldn't be apparent unless the values in sheet1 existed also in sheet2, then we'd have something to look for. Formatting them the same is usually a good idea though.
The *1 in the formula that I gave you (and sktneer as well) will take care of it, as long as you have only numbers in column A of Sheet2.
Hope this helps.
Pete
Thanks Pete!Originally Posted by Pete_UK
Had to come back to this today! Realize I didn't marked he thread as solved. Thanks all
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks