0

Please bear with my explanation here - I want to be as clear as possible. I have about 8 different tables (different categories), which contain ranges that correspond to point values. I have been using VLOOKUP for each category to calculate the number of points based on which range your input falls into. These are 2 examples of the charts w/lookup.

In another tab, I have an input column, a "points" column, and a drop down list of all 8 categories. Again, each category corresponds to a different VLOOKUP. I am trying to input an amount, and based on which category I select from the drop down, have Excel output the number of points I would earn (I have been trying to do this using VLOOKUP). It works for the first row, since in my final VLOOKUP it checks the first input value - but when I drag down the formula for many rows, it does not update to correspond to the input in that row (it only looks at that first input cell). How can I make it so that the initial vlookup takes the input of whatever row I'm currently in?

(see highlighted for relevant columns)

This is quite difficult to explain - let me know if any clarification is necessary.

  • In the interest of clarify, by chart to you mean table? It would also be helpful to have sample data and the formulas you are using – cybernetic.nomad Jun 21 '19 at 16:26
  • yes table - will edit – EmilyAnn DeChiara Jun 21 '19 at 17:45
  • @EmilyAnnDeChaira, Could you please share the VLOOKUP formula you have use I think in Col X,,, and getting error will help us to understand the source you have tried with it!! And the Formula in Col P also (2nd Screen Shot) – Rajesh S Jul 27 '19 at 8:00
0

Emily,

Instead of VLOOKUP, have you tried a nested IF? If not, try

=IF(B4>=A18,C18,IF(B4>=A17,C17,IF(B4>=A16,C16,IF(B4>=A15,C15,IF(B4>=A14,C14,IF(B4>=A13,C13,IF(B4>=A12,C12,IF(B4>=A11,C11,IF(B4>=A10,C10,IF(B4>=A9,C9,IF(B4>=A8,C8,IF(B4>=A7,C7,ERROR))))))))))))

in cell B5 under "NI Contracts TVC Range".

Let me know whether that does what you need. If it does, we can work on the next step.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.