IF AND with vlookup

[garypea123]

Hi Guys,

New to this site (1st post I think)..

I would like some advice on the attached file. I am trying to put in a formula if F5 where it is looking at the information in B5 and then looking at the table numbers in L:M.

If B3 falls between the numbers in L:M then it return the value in N.

I assuming this is an IF(and) formula with possible VLOOKUP...

I have attached test file to help. And have manually populated my required results..

Thanks,
Gary

 

[jasonb75]

Maybe

=LOOKUP(B5,$L:$L,$N:$N)

It's preferable if you use XL2BB to upload examples (button on reply box toolbar), not screen captures.

 

[garypea123]

Yes, that works in theory, and thank you ever so much for the advice on upload..

However, my concern is that it is not really looking at the numbers between L:M. I have supplied an example where it shows that if I use a number which does not exist that it returns an value which is not true.

In essence I want it to look at B4 (F4), B5 (G4), B6 (H4) and then look at L:M and if the number falls between it will choose the weight in Column N.

I guess my point is that should a number not fit into between min and max then I need to highlight an issue to me (Error code or something). It should not happen as my weights should have no gaps. But if there was an issue than I prefer it not to suggest a band weight that actually does not exist (for example 1000 says band weight 799 - 998g which would be wrong as the max is 999...

Hopefully that makes sense

I

 

[jasonb75]

It should not happen as my weights should have no gaps.

Actually there are gaps which I missed, here's the fixed formula.

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N
3	Min	Mid	Max		Band	Band	Band			Tier	Min	Max	Band
4	800	900	1000		799g - 998g	799g - 998g	999g - 1198g			1	799	998	799g - 998g
5	1001	1200.5	1400		999g - 1198g	1199g - 1398g	1399g - 1598g			2	999	1198	999g - 1198g
6	1401	1550.5	1700		1399g - 1598g	1399g - 1598g	1599g - 1798g			3	1199	1398	1199g - 1398g
7	998.5	1398.5	1798.5		Error	Error	Error			4	1399	1598	1399g - 1598g
8										5	1599	1798	1599g - 1798g
9										6	1799	1998	1799g - 1998g
10										7	1999	2198	1999g - 2198g
11										8	2199	2398	2199g - 2398g
12										9	2399	2598	2399g - 2598g
13										10	2599	2798	2599g - 2798g
14										11	2799	2998	2799g - 2998g
15										12	2999	3198	2999g - 3198g
Sheet4

Cell Formulas
Range	Formula
F3:H3	F3	=$N$3
F4:H7	F4	=IFERROR(INDEX($N:$N,AGGREGATE(15,6,ROW($L$4:$L$15)/($L$4:$L$15<=B4)/($M$4:$M$15>=B4),1)),"Error")
M4:M15	M4	=L4+199
N4:N15	N4	=L4&"g - "&M4&"g"
L5:L15	L5	=L4+200

 

[garypea123]

Sorry for late reply!! - Seems to work perfectly... This exercise is on delay at the minute, but I am revisiting