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

B5rocksass

New Member
Joined
Jan 10, 2017
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
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.

ITEM50075010001500
CARD HOLDERS1.291.221.191.15
TOTE BAGS2.472.42.372.33


 

Some videos you may like

Excel Facts

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,502
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 3, 2013
Messages
220
Sheet1

CDEFGHIJK
3Range1Range2Range3Range4
4ITEM50075010001500ITEMITEM1000
5CARD HOLDERS1.291.221.191.15CARD HOLDERSCARD HOLDERS1.19
6TOTE BAGS2.472.42.372.33TOTE BAGSTOTE BAGS2.37

Formeln der Tabelle
ZelleFormel
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
Joined
Jun 12, 2014
Messages
48,502
Office Version
  1. 365
Platform
  1. Windows
@marious
We have a new way of posting data to the board, called XL2BB, rather than using XlJeanie
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,502
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
48,502
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 10, 2017
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
48,502
Office Version
  1. 365
Platform
  1. Windows
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. ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,608
Messages
5,548,968
Members
410,886
Latest member
sjohn627
Top