Hi.
I have the following formula that I copied down a column of 100 rows. The formula worked on all but 2 of the cells. Those 2 cells return #VALUE!.
=ROUNDUP(J32/VLOOKUP(B32,Recipes!$A$5:$O$104,10)*2,0)/2
When I change the array to $A$5:$0$59 the formula works. If I choose any number between $O$60 & $O$104 I get the error.
I tried deleting the formula out of both cells and manually typing them in, but that did not work.
Any help is appreciated.
I expect you'd get #VALUE! error if the VLOOKUP returns a text value from column J - is that possible?
Try just using the VLOOKUP on it's own - what do you get in the rows where you got the errors?
Audere est facere
The J column contains the number 26.5, which is a sum of 5 other cells. It should be divided by a number that is being looked up on another page via VLOOKUP and then rounded up to the nearest .5.
It worked as it should on the cells above it, but for some reason it doesn't on this cell and the one directly below it.
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
If posting code please use code tags, see here.
Thanks Norie.
I am not sure how to make a mock-up. This spreadsheet is huge and all of the pages refer to each other. I will see if I can come up with something tomorrow.
I actually meant column J on the other worksheet, that’s the column (column 10) that your VLOOKUP is returning a value from.Originally Posted by LKERN
For debugging purposes just try VLOOKUP on its own, i.e just
=VLOOKUP(B32,Recipes!$A$5:$O$104,10)
what do you get with that on problem rows? I suspect it’s returning a text value and then that leads to the error when you try to do some mathematical operation on a text value.
Btw, your VLOOKUP has no 4th argument which means that it will only work correctly if Recipes!A5:A104 is sorted ascending, is that the case? If not, and/or you expect an exact match then you should add FALSE AS THE 4th argument in VLOOKUP
daddylonglegs,
Thanks so much for your help. I checked the J column (column 10) on the VLOOKUP sheet and it was the number "5.00", formmatted as the number, not text.
I had also already tried deconstructing the formula and found that when I changed the array to end at anything lower than $O$60 the formula worked.
I do already have the list sorted ascending alphabetically.
I added the 4th argument to the VLOOKUP and it worked! I'll go in and add that on all of the VLOOKUP formulas.
I am learning Excel by Googling what I need to do, so I don't always get all of the information I need and I don't know what I don't know a lot of the time.
I appreciate all of the responses that I received to my question.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks