CODEWITHSUNDEEP
excelexcel-formula
croco88
croco88

Reputation: 55

Excel - I want to lookup 1 table that is between a number range and is equal to a value

My excel sheets has the following tables

MIN     MAX     name_a  name_b  name_c  name_d
0       50      5.6     5.35    5.1     4.85
50      100     5.55    5.3     5.05    4.8
100     200     5.45    5.2     4.95    4.7
200     500     5.4     5.15    4.9     4.65
500     1000    5.325   5.075   4.825   4.575
1000    2000    5.255   5.005   4.755   4.505

I have 2 more cells which correspond to names and a weight ie;

1) 433

2) name_c

The function would need two look up tables I believe;

1) The weight; if weight is more than MIN & less than MAX then use this row

2) The name if the name is equal to this, then use this column

I am struggling trying to combine 2 lookup functions to return me a value

The example should see that the value sits between 200 and 500 and should look at this row, and then it should see that the name is equal to name_c and therefore return me 4.9

Any help would be greatly appreciated.

Upvotes: 1

Views: 51

Answers (1)

BigBen
BigBen

Reputation: 49998

You could use SUMIFS with an INDEX/MATCH to get the correct column.

=SUMIFS(INDEX(C1:F7,,MATCH(H4,C1:F1,0)),A1:A7,"<="&H3,B1:B7,">="&H3)

enter image description here

Upvotes: 1

Related Questions