Need VLOOKUP to detect last occurrence, or LOOKUP to not expect alphabetical sorting

[BBL]

Hi all,

Here is the spreadsheet I am working on: http://www.voodooguild.net/veev/veev...readsheet.xlsx

In rows 6-10 I enter my own spell names (for a video game) accompanied by how frequently they are used. The order that they are entered in represents their priority (most important at the top). In rows 17 and below I am trying to simulate a 5+ minute sequence of these spells, using the data I enter at the top.

Unfortunately I have run into a problem where the LOOKUP function I am using in cell A22 expects the range/array I am searching to be sorted alphabetically. You can observe this by changing cells A9 and A20 to "TestBloom2" and noticing no error, but if you change them to just "Bloom" it breaks the function.

So basically I suppose I am looking for a way to get VLOOKUP to detect the last occurrence of an array (like how LOOKUP(2,1/($A$17:A21=Spec1Spell1),$D$17:D21) does), or for the LOOKUP function to not expect the lists to be sorted (like how I use the false parameter in cell F17). Or if someone has an idea to do this in a much simpler/cleaner fashion I'd be open to that as well. I'm far from being an Excel expert.

Thanks for your time!

[rajkote]

hi can u send me asmple file

[DGagnon]

looking at your file, im not sure what your look up criteria or range should be, can you be more specific on that front?

[BBL]

Sure, I'll try to explain a bit more.

Basically the orange background cells are all predefined by me. The first row/cell that is not is A22.

In this cell I want to scan cells $A$17:A21 for the last time the text in A6 (Radiant Spores) was used. The last time Radiant Spores was detected was in A17. Then it checks to see how many seconds ago that was by looking at column D for that same row. It occurred at the 0 second mark. The time is currently 5.9 seconds (as given by D21). D21 minus D17 = 5.9. However, the "average reuse time" of Radiant Spores is 15 seconds (defined by C6). Since D21-D17 < C6, not enough time has passed to use it again, so then it proceeds to check to see if enough time has passed for the 2nd-most-important spell (Ruin).

It's pretty confusing when phrased like that probably, but that's about as clear as I can explain it. Basically I make a table including spell names, the length they take to be used, and how frequently they are used -- all while listing them in order of priority. Then I just try to simulate a 5'ish minute scenario of using them.

One thing I was thinking I could maybe do is just create a new column in that cell with numbers "1, 2, 3" etc to specify the priority, and pull up the cells that way.

[WHER]

In cells C17, C18 etc... of sheet "chloro-pyro simulation" add a "0" or "False" as fourth parameter ( range_lookup) to your Vlookup. That way you're looking for an exact match and the "table array" doesn't need to be alphabetically sorted.
So in c17:

Please Login or Register  to view this content.

This should avoid the N/A error when changing "TestBloom2" to "Bloom"

[BBL]

Ah, that worked... amazing, thanks! Must have completely forgotten to edit that column when I was adding the false parameter.