# Need Help - INDEX/MATCH if value to find falls within a range

#### B5rocksass

##### New Member
Hello. I'm having difficulty creating this formula. I need to find a value that falls on or between another value, then returns another value. I've tried IF statements and INDEX/MATCH formulas, and ultimately i think i need both. Here is a clip of what i'm working with. Say I have 1,200 pcs of an item. My price break falls along this table: 500, 750, 1000, 1500 and the value I need to return is the cost of that quantity for that range. Please help! Thank you.

 ITEM 500 750 1000 1500 CARD HOLDERS 1.29 1.22 1.19 1.15 TOTE BAGS 2.47 2.4 2.37 2.33

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### Fluff

##### MrExcel MVP, Moderator
What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.

Also for 1200 pcs should it return the price for 1000 or 1500?

#### marious

##### Board Regular
Sheet1

 C D E F G H I J K 3 Range1 Range2 Range3 Range4 4 ITEM 500 750 1000 1500 ITEM ITEM 1000 5 CARD HOLDERS 1.29 1.22 1.19 1.15 CARD HOLDERS CARD HOLDERS 1.19 6 TOTE BAGS 2.47 2.4 2.37 2.33 TOTE BAGS TOTE BAGS 2.37

Formeln der Tabelle
 Zelle Formel K5 {=INDEX(\$D5:\$G5,1,SUM(--((\$D\$4:\$G\$4/\$K\$4)<=1)))} K6 {=INDEX(\$D6:\$G6,1,SUM(--((\$D\$4:\$G\$4/\$K\$4)<=1)))}
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

#### Fluff

##### MrExcel MVP, Moderator
@marious
We have a new way of posting data to the board, called XL2BB, rather than using XlJeanie

#### Fluff

##### MrExcel MVP, Moderator

A non array formula is you have 365
+Fluff v2.xlsm
ABCDEFGHI
1Less thenGreater than
2ITEM5007501000150012001200
3CARD HOLDERS1.291.221.191.15Tote bags2.372.33
4TOTE BAGS2.472.42.372.33Card holders1.191.15
Data
Cell Formulas
RangeFormula
H3:H4H3=XLOOKUP(\$G3,\$A\$3:\$A\$4,XLOOKUP(H\$2,\$B\$2:\$E\$2,\$B\$3:\$E\$4,"",-1),"",0)
I3:I4I3=XLOOKUP(\$G3,\$A\$3:\$A\$4,XLOOKUP(I\$2,\$B\$2:\$E\$2,\$B\$3:\$E\$4,"",1),"",0)

#### Fluff

##### MrExcel MVP, Moderator
If you don't have 365 & you want the lower value you can use
+Fluff v2.xlsm
ABCDEFGH
1Less then
2ITEM500750100015001200
3CARD HOLDERS1.291.221.191.15Tote bags2.37
4TOTE BAGS2.472.42.372.33Card holders1.19
Data
Cell Formulas
RangeFormula
H3:H4H3=INDEX(\$B\$3:\$E\$4,MATCH(G3,\$A\$3:\$A\$4,0),MATCH(\$H\$2,\$B\$2:\$E\$2,1))

#### B5rocksass

##### New Member
The version of Excel I am using is Prof 2016.
I am looking for the lower number since i met the price break for 1,000 but not 1,500.
Thank you Fluff!
Thank you everyone for all your help!

#### Fluff

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback.

Please don't forget to update your account details to show your version of Excel & then scroll down & click save.

Replies
4
Views
116
Replies
7
Views
152
Replies
3
Views
380
Replies
8
Views
423
Replies
2
Views
205