I'm working on an excel 2010 worksheet that uses a table that references a secondary table to generate a list of values.
More specifically, I have two tables: Table_Players and Table_ChallengeCalc. In Table_Players, I have the columns Players, Experience, and Level. I input the total number of points that a given player has under the experience table, and using this formula the level column tells me what level the player is:
=LOOKUP(C6, {-1;0;300;900;2700;6500;14000;23000;34000;48000;64000;85000;100000;120000;140000;165000;195000;225000;265000;305000;355000},{"0";"1";"2";"3";"4";"5";"6";"7";"8";"9";"10";"11";"12";"13";"14";"15";"16";"17";"18";"19";"20"})
Table_ChallengeCalc resides on a different sheet in the workbook, and references the Level value in Table_PlayerListing to pull a list of additional values from another table, Table_Difficulty.
Before I implemented the above lookup formula, I was just manually inputting the player's levels and in turn, the Table_ChallengeCalc was pulling the correct values for the remaining columns, using this to load the player's assigned level on Table_Players:
=VLOOKUP([Players],Table_PlayerListing[Level],3,FALSE)
(Here's what it looks like when it works correctly)
However, as soon as I added the first LOOKUP value, the second VLOOKUP immediately became #N/A and in turn, so did all of the rest of the reference columns. If I remove the first lookup and manually enter the level again, the VLOOKUP immediately starts working again. I tried using a nested INDEX/MATCH statement, but it had the same results. As long as I'm using that first LOOKUP, Table_ChallengeCalc can't seem to pull the level value.
If I didn't know any better, it would seem to me that the workbook is trying to run the VLOOKUP simultaneously, if not immediately prior, the LOOKUP formula. Any ideas on how I can counter this, or is this one of those things where I've kind of hit the limitations of what an excel workbook can do without additional software like VBA?
Aucun commentaire:
Enregistrer un commentaire