Find entity in a list of entities and give back another cell value based on which category it belongs to in excel

Question

I want to find a nice way to get a specific value based on a category and another characteristic. I'll try to to exemplify it below;

Problems:

1) I want to check what category a certain entity belongs to.

2) Based on the answer in 1) AND another factor, lets call it experience points, I want to be able to get the value of the intercept between the two.

Data:

Entity............................XP.............A.........................B......................XP............A.............B Gandalf.........................80..........Gimli....................Frodo.................0-50..........2.5.........3.1
Frodo............................21........Gandalf.....Witch-king of Angmar.....51-100......3.3.........4.2
Gimli.............................39............................................................... 101-150.....4.6..........7.4
Witch-king of Angmar..140

I.e. what I really want to find out is that Gandalf would have a value of 3.3 and the Which-king of Angmar would have 7.4 without reading the damn tables. I would highly appreciate a smart and lean way of doing this. I've tried matching, indexing, Vlookuping and building simple macros without finding that solution that'll make your stomach tickle in a special way. Please help me!

Answer 1

You've already given the answer. vlookup and index are the right functions to use.

My suggestion would be not to try to put it all in one formula/cell but to split the formulae up. Right next to the field in which the Entity name would be entered, do a VLOOKUP for the XP. Next to that, use MATCH to find the category. Finally, use INDEX to find the value based on the other two columns. You can use ROUND to roundup the XP to the right value (ROUND(XP/50) in your example).

Hope this makes your stomach tickle ;-)

Answer 2

If I've understood this correctly, you want to

Index column 1, Match column 2 to column 5, and Return either the value column 6 or 7, depending on whether the entity is in column 3 or 4

It looks like you want to use Index(Match) to do most of the work, as agtoever suggests. Also he's right on the money with you'll have trouble matching the specific entity xp to the band xp oin what amounts to the second table (on the right).

First thing to do is to round the XP as stated, and also to change the values in the right most XP column to single values to match.

The next thing you need to do is let excel know if the entity is in column a or column b. It looks like your whole lay out is one table atm. I'm going to suggest you split it into three.

Table 1

Entitiy | Specific Entitity XP | Entitiy XP Band| A or B | Final Return

Table 2

A | B

Table 3

XP Band | Column A | Column B

This means that if you map a or b to each entitity in table 1, you can then check table 2, to return the correct value from A or B

Step 1: XP

1.1: change the XP Band to a single value (probably maximum) so it looks like

50 100 150

1.2: calculate which band the entitiy is in by ROUND as above.

Step 2: A or B

Write a formula in the fourth column that indexs the entitiy name, then checks if it is in column a in table 2. If it is return "A", if not, return "B"

something like

=IF([entitity in this row] = [range A], "A", "B")

Step 3: Return Value

Write a formula in the fifth column that checks the fourth column, and then then checks the third table for the final number, this could be using nested IF statments, AND or

something like

=IF([entitiy XP band] = [XP band], IF([entity A or B] = "A",[return A in table 3]),[Return B in table 3])

I hope this psuedo code explanation works. If you need more clarifiaction I'll write up a specfic example when I get home this evening.

Answer 3

For some reason this question didn't get added as my own so unfortunately I've missed these answers. Anyhow, thank you very much, you understood the problem correctly and found a nice solution.

The actual data is quite large, the XP Bands are divided into a lot more categories, table 2 consists of A-G instead of A-B and there are actually 3 different "table 3" depending on yet another category (similarly built as table 2 using X Y Z). Due to all this I chose a similar but slightly different solution which I thought I might share for any academic purpose.

Step one: Get the XP band to match was solved by:

=IF(AND(0<=[XP in this row], [XP in this row]<51), "0-50", IF(AND(50<[XP in this row], [XP in this row]<101), "51-100", "101-150"))

Step 2_ Getting category 1, A-G:

=IF(ISERROR(MATCH([entity in this row],[range A],0)),IF(ISERROR(MATCH([entity in this row],[range B],0)), "C", "B"), "A")

Step 3: Finding what table to use (category 2) X-Z:

Solved in the same way as step 2.

Step 4: Finding the solution for each entity and for all types X-Z

=INDEX([table X], IF([XP]=[XP band1], 2, IF([XP]=[XP band2], 3, 4)), IF([category1 for entity]="A",2,IF(([category1 for entity]="B", 3, 4)))

Step 5: The final step where I choose which value of X, Y and Z to use:

=IF([category 2]="X", [table X], IF([category 2]="Y", [table Y], [table Z]))

Perhaps not the prettiest solution but it sure did the trick and will save me a lot of work.

Once again, thank you for the proposed solutions, it's likely that you might have solved it in a leaner fashion.