# Excel Index Matching between Value Range

3055 views
2

I have a problem when trying to do index matching. I want to match the item from table 1 and table 2 to obtain the Sample field. If Item from table 1 is matched with table 2 Item and the Lot size from table 1 is in between table 2 LotSizeFrom and LotSizeTo. It will obtain the Sample field from table 2.

table 1 table 2 Expected Output 12

try the following formula. You will need to sort your data by item ascending and then by lot size from, also ascending. The column Lotsize to is not required. See the screenshot for the cell references.

``````=INDEX(INDEX(\$D\$1:\$D\$19,MATCH(H2,\$A\$1:\$A\$19,0)):INDEX(\$D\$1:\$D\$19,MATCH(H2,\$A\$1:\$A\$19,1)),MATCH(G2,INDEX(\$B\$1:\$B\$19,MATCH(H2,\$A\$1:\$A\$19,0)):INDEX(\$B\$1:\$B\$19,MATCH(H2,\$A\$1:\$A\$19,1)),1))
`````` posted this
3

`INDEX()` combined with `SUMPRODUCT()` should work :

`=INDEX(\$I\$2:\$I\$18,SUMPRODUCT(ROW(\$F\$2:\$F\$18)-1,(\$F\$2:\$F\$18=B7)*(A7>=\$G\$2:\$G\$18)*(A7<\$H\$2:\$H\$18)))` posted this