Whenever You need to Index with multiple criteria (2, 3 or more match criteria) just follow the below example:
Problem:
From Table A (Cell range D4:F13), I need to find out the Unit Price of Plant in every Month for summary in Table B (cell range H4:J6).
Solution:
This can be done in various ways, but due to this article is based on Index() and Match(). I will go for it. But wait a minute, I will use an Array Formula here. So how to do it? Simple:
Step 1:
In J2 Cell of Table B under Unit_Price, enter the below formula and Press Ctrl+Shift+Enter:
Step 2:
After pressing the Ctrl+Shift+Enter the formula automatically add two curly braces {} in front and end of the formula. So that, computer will read it as an Array Formula.
Step 3:
This will returns the value 52 for Jan-Mint. Now drug it. Then it will returns 41 and 71 for Feb-Cilantro and Mar-Bitter Gourd.
Problem:
From Table A (Cell range D4:F13), I need to find out the Unit Price of Plant in every Month for summary in Table B (cell range H4:J6).
Image 1: Table A and Table B
Solution:
This can be done in various ways, but due to this article is based on Index() and Match(). I will go for it. But wait a minute, I will use an Array Formula here. So how to do it? Simple:
Step 1:
In J2 Cell of Table B under Unit_Price, enter the below formula and Press Ctrl+Shift+Enter:
=INDEX($E$5:$E$13,MATCH($H5&$J5,$D$5:$D$13&$F$5:$F$13,0)
Image 2: Table B - Result
Step 2:
After pressing the Ctrl+Shift+Enter the formula automatically add two curly braces {} in front and end of the formula. So that, computer will read it as an Array Formula.
Step 3:
This will returns the value 52 for Jan-Mint. Now drug it. Then it will returns 41 and 71 for Feb-Cilantro and Mar-Bitter Gourd.