Sunday, August 2, 2009

Can Excel do this task?

I have a two columns (A and B) with a range of dollar amounts. For example A has $1, B has $2 with C having a percentage that anything between $1 and $2 would be associated with. In the next row down I have in Column A $2.01, B has $3 with C having another percentage associted with it. The table follows the same way. Can you use a vlookup function to type in a dollar amount and look to see what percentage is associated with it? I'm sure you can, but can't figure out the syntax!





Thanks!

Can Excel do this task?
You don't necessarily need what is in Column B to do the VLOOKUP as long as the values you have in Column A are in ascending value.





The last argument in the VLOOKUP function is a True/False option. Setting it to False causes the function to try to find an exact match in your Table Array. If it does not find one, the formula produces an #N/A.





The default, if you leave the last argument blank, is True. True is what I call "The Price is Right" option. It stops the lookup at the value in your Table Array at the highest value without going over.





So, this formula should produce what you want (where the dollar amount you are typing in is in F1):





=VLOOKUP(F1,A1:C25,3)

sending flowers

No comments:

Post a Comment