Fine value in chart based off two critera

[thunderchld]

First, thanks for the help. I hope to explain this properly. I'm also open to VBA/Macro/Add-Ins if that is a possible solution.

Basic language:
I have a workbook for a character database. Sheet 1 contains summary of a Character information (Race, XP, Level, Lives). I am currently keeping this up-to-date manually. I'd like this populate based off values in other sheets. Sheet 2, for example, contains Race information. Race, Level, XP, Body, Live increase. I want to find what level someone should be based off comparing sheet 1 to 2.

Excel example:

Sheet 1

Please Login or Register  to view this content.

Sheet 2

Please Login or Register  to view this content.

The expected results should return:
Ansley - Level 5 (Row 6, Column C)
Bert - Level 2
Carter - Level 4

I can add another cell to have XP Min and XP Max if need be, but I could not get my logic to work around that either.

[lecxe]

Hi
Welcome to the board

Try in Sheet1!D2:

=LOOKUP(C2,Sheet2!$B$2:$B$13/(Sheet2!$A$2:$A$13=B2),Sheet2!$C$2:$C$13)

Copy down.

Remark:
This solution assumes the setup of your table in Sheet2, namely XP in ascending order for each race

[thunderchld]

Thank you! I could sort to make sure XP is in ascending order; but there is a chance that the sort could change (vs just being filtered). This gives me an amazing start! I really appreciate it.

[lecxe]

I'm glad it helped.

If you say that the table in Sheet2 may not have XP values in ascending order, you may use, for ex.:

=SUMPRODUCT((Sheet2!$A$2:$A$13=B2)*(Sheet2!$B$2:$B$13=AGGREGATE(14,6,Sheet2!$B$2:$B$13/(Sheet2!$A$2:$A$13=B2)/(C2>=Sheet2!$B$2:$B$13),1)),Sheet2!$C$2:$C$13)

[mcmahobt]

Assuming your answer lies in D2, try the following ARRAY formula entered by pressing CTRL+SHIFT+ENTER:

D2: =INDEX(Sheet2!$C$2:$C$13,MATCH($C2,IF(Sheet2!$A$2:$A$13=$B2,Sheet2!$B$2:$B$13),1))

Data does not need to be in any particular order.

[thunderchld]

Thanks!

I did try all three, and I'll keep those in my pocket. They did all work and provided the functionality I needed. For this particular issue, the array worked great! I'll be using the others in the spreadsheet as well! Thank you!