# Excel - Vlookup within Vlookup dependent on drop-down

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

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".