Lookup from 3 parameters in Excel

Question

Lookup from 3 parameters in Excel

Bijak R. Ahadito 1

Hello, I'm working on a database of Pokemon Unite's Held Items and I want to look up a value based on 3 parameters: the item, its stat, and the value of its stat at a certain item level (i.e. Aeos Cookie's HP stat at Level 30). The table is shown below.

I tried the INDEX-MATCH trick, but it didn't work. Perhaps because the two parameters were in vertical list and the other one is horizontal.

Please help. Thanks in advance.

P.S.: I'm running an university-licensed version of Office 2016

2 answers

Your answer

Answer 1

Viki Ji_MSFT 4,471

Hi @Bijak R. Ahadito ,
Welcome to Q&A forum!

Please check if the following formula is helpful to you:

INDEX($C$2:$AF$5,MATCH(A8&B8,$A$2:$A$5&$B$2:$B$5,0),MATCH($C$7,$C$1:$AF$1,0))

Any updates, please let me know.

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Bijak R. Ahadito 1 Reputation point

2021-09-29T10:40:27.853+00:00

Hello @Viki Ji_MSFT , thanks for the answer.

Unfortunately, that formula solves into #VALUE! error. Looks like Excel tries to put a value on the array.
Here's the evaluation:
Viki Ji_MSFT 4,471 Reputation points

2021-10-06T06:56:49.863+00:00

Hi @Bijak R. Ahadito ,
I apologize for reply late.
Please change the formula according to the reference parameters, based on the screenshots you provided, please try this:
INDEX($C$2:$AF$74,MATCH("Aeos Cookie"&"HP",$A$2:$A$74&$B$2:$B$74,0),MATCH(30,$C$1:$AF$1,0))

Answer 2

Herbert Seidenberg 1,191

Excel 365 Pro Plus with Power Pivot and Power Query.
Select 2 row and 1 column parameters with Slicer.
No formulas, no VBA macro.
https://www.mediafire.com/file/02gfs071z5a0eie/09_29_21.xlsx/file
https://www.mediafire.com/file/yqm66akv31rri95/09_29_21.pdf/file

Share via

Lookup from 3 parameters in Excel

2 answers

Your answer