CODEWITHSUNDEEP
excel
revvy
revvy

Reputation: 151

VLOOKUP using CONCATENATE and value between ranges

I have a table of fixed parameters as follows

Store   Product     Lower Weight    Upper Weight    Unit price
NYC     Apple       0.00            3.00            0.159
NYC     Apple       3.01            5.00            0.162
NYC     Orange      0.00            2.00            1.112
NYC     Orange      2.01            10.00           1.012
NJY     Apple       0.00            3.50            2.20
NJY     Apple       3.51            10.00           2.05
NJY     Mango       0.00            5.00            5.12
NJY     Mango       5.01            105.00          5.22

I have another table with exact weight of each product at each store which I want to use to determine the unit price.

Store   Product     Exact Weight    Unit price
NYC     Apple       0.58            [=FORMULA]
NJY     Mango       2.29            [=FORMULA]

What is the formula to determine the exact unit price in the 2nd table? I would typically use vlookup/concatenate but I am now stuck because this trick wouldn't work with ranges.

Upvotes: 2

Views: 122

Answers (2)

QHarr
QHarr

Reputation: 84465

You can use Index Match and enter as an array formula with Ctrl+Shift+Enter

=INDEX($E$2:$E$9,MATCH(1,($A$2:$A$9=$H4)*($B$2:$B$9=I4)*(J4>=$C$2:$C$9)*(J4<=$D$2:$D$9),0))

Data:

data

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152505

Use SUMIFS:

=SUMIFS(E:E,A:A,G2,B:B,H2,C:C,"<="&I2,D:D,">="&I2)

enter image description here

Upvotes: 2

Related Questions