Value Lookup Function for non reference sheet values
Reference to the formula in cell F3 of the attached sample sheet, I would like please a modification to this formula in order to find out what would be the Calories value if non exact numbers is entered in F4 and F5.
For example if a distance of 11 is entered in cell F4 and a weight off 80 is entered in cell F5, what would be the resulting Calories value in cell F3?
Last edited by Khaldon; 06-13-2011 at 03:19 AM.
Hi Khaldon,
I think you are asking about the Match_Type (the last argument) in your Match functinos. If you put a zero as the last argument it must match exactly. You need to try putting in a 1 or -1 for the zeros to see what happens.
Read http://office.microsoft.com/en-us/ex...010062414.aspx and pay attention to the Match_Type.
I also have a concern about having words in row 2 and column A. Perhaps you can move the words "weight" and "Dist" off of the range you are matching and still have your table meaningful.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Hi,
Have a look at the different match types available for the Match function. Assuming you would want to round down you may need to change the order of your lookup array to suit your purpose.
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
Re: Value Lookup Function for non reference sheet values
Hi Marvin,
Thank you for your reply.
Please refer to the new table attached that I created as a sample to what am I trying to achieve.
I tried to replace the zero from the old Match type with -1, but unfortunately it reflected a #N/A!
Can you advice me please what is wrong with my new sample table, why the mach is not working with -1!
Hi,
See if the attached isn't what you want.
That is close, but what I really need is to see is the closest value result of Calories and if that is not possible, the next higher will do.
So for the give example figures 11 / 80 in cells F4 and F5 respectively, the resulting value in cell F3 should read 331 and if that is not possible, is to read the next higher which is 431 please.
If you want it to read 331 because you want the Distance to go to next higher number and weight to stay at previous lower number, then try:
If you want both Distance and Weight to go to next higher number to get 431, then try:
Please Login or Register to view this content.
I am not sure why you have a 3 in A2, though...Please Login or Register to view this content.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Hi,
See if this attached works now.
It is working now, many thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks