Vlookup returning in #NA, why?

[babettedv]

Hi there,

Please help me out on the Vlookup - I keep getting #NA.
Aim is to add the average costs based on the weekly volume range!

Thanks!!

VLA No Meat - cost overview 2021-01-29 (003).xlsx
	B	C	D	E	F	G	H	I	J	K
3		Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20
4
5	Volume (KG)	124,478	94,455	106,678	134,246	125,441	83,044	102,223	119,772	114,474
6
7
8	Fixed costs / KG	0.75	0.75	0.75	0.75	0.75	0.75	0.75	0.75	0.75
9	Total fixed costs	93,359	70,841	80,009	100,685	94,081	62,283	76,667	89,829	85,855
10
11	Fixed costs / KG	#N/A	0.91	0.91	0.91	0.91	0.91	0.91	0.91	0.91
12	Total fixed costs	#N/A	86,322	97,492	122,687	114,640	75,893	93,421	109,459	104,617
13	Difference	#N/A	(15,480)	(17,484)	(22,002)	(20,559)	(13,610)	(16,753)	(19,630)	(18,761)
14
15	# of days	31	28	31	30	31	30	31	31	30
16	# of weeks	4.429	4.000	4.429	4.286	4.429	4.286	4.429	4.429	4.286
17	Volume per week (KG)	28,108	23,614	24,089	31,324	28,325	19,377	23,083	27,045	26,711
18
19
20
21
22
23	Total volume VLA VOLUME PER WEEK	TMF average cost coverage	Total amount
24	37,500	0.914	34,271
25	50,000	0.685	34,271
26	62,500	0.548	34,271
27	75,000	0.457	34,271
28	87,500	0.392	34,271
29	100,000	0.479	47,917
30	112,500	0.547	61,563
31	125,000	0.602	75,208
32	137,500	0.646	88,854
33	150,000	0.683	102,500
34	162,500	0.715	116,146
35	175,000	0.742	129,792
Volume calculatie

Cell Formulas
Range	Formula
C5:K5	C5	='P&L Trend Consol YTD'!B57
C8:K8	C8	=AVERAGE('Current VL'!$J$6:$J$15)
C9:K9	C9	=C5*C8
C11	C11	=VLOOKUP(C17,$B$24:$C$35,1,TRUE)
D11:K11	D11	=HLOOKUP(D17,$B$24:$D$35,1,TRUE)
C12:K12	C12	=C11*C5
C13:K13	C13	=C9-C12
C16:K16	C16	=C15/7
C17:K17	C17	=C5/C16
B24:C24	B24	='Possible deal structure'!B44
D24:D35	D24	=B24*C24
B25:C25	B25	='Possible deal structure'!B43
B26:C26	B26	='Possible deal structure'!B42
B27:C27	B27	='Possible deal structure'!B41
B28:C28	B28	='Possible deal structure'!B40
B29:C29	B29	='Possible deal structure'!B39
B30:C30	B30	='Possible deal structure'!B38
B31:C31	B31	='Possible deal structure'!B37
B32:C32	B32	='Possible deal structure'!B36
B33:C33	B33	='Possible deal structure'!B35
B34:C34	B34	='Possible deal structure'!B34
B35:C35	B35	='Possible deal structure'!B33

 

[maabadi]

Vlookup with True OptionSearch for value less than your criteria and Cell C17 is Smaller than your first criteria.
If you want to Return the Value bigger than your Criteria Then Use this

=INDEX($B$24:$B$35,IFNA(MATCH(C17,$B$24:$B$35,1),0)+1)