Excel Index Matching between Value Range

3055 views excel
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

enter image description here

table 2

enter image description here

Expected Output

enter image description here

answered question

2 Answers

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))

enter image description here

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)))

enter image description here

posted this

Have an answer?

JD

Please login first before posting an answer.