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

[B5rocksass]

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

 

[Fluff]

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]

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]

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

 

[Fluff]

A non array formula is you have 365

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I
1								Less then	Greater than
2	ITEM	500	750	1000	1500			1200	1200
3	CARD HOLDERS	1.29	1.22	1.19	1.15		Tote bags	2.37	2.33
4	TOTE BAGS	2.47	2.4	2.37	2.33		Card holders	1.19	1.15
Data

Cell Formulas
Range	Formula
H3:H4	H3	=XLOOKUP($G3,$A$3:$A$4,XLOOKUP(H$2,$B$2:$E$2,$B$3:$E$4,"",-1),"",0)
I3:I4	I3	=XLOOKUP($G3,$A$3:$A$4,XLOOKUP(I$2,$B$2:$E$2,$B$3:$E$4,"",1),"",0)

 

[Fluff]

If you don't have 365 & you want the lower value you can use

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H
1								Less then
2	ITEM	500	750	1000	1500			1200
3	CARD HOLDERS	1.29	1.22	1.19	1.15		Tote bags	2.37
4	TOTE BAGS	2.47	2.4	2.37	2.33		Card holders	1.19
Data

Cell Formulas
Range	Formula
H3:H4	H3	=INDEX($B$3:$E$4,MATCH(G3,$A$3:$A$4,0),MATCH($H$2,$B$2:$E$2,1))

 

[B5rocksass]

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]

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.