Thread: lookup a number in a range.

I have a number in column A and i want to find out if that there are any numbers within a 20% range up or down (so the range would be A1*.8 through A1*1.2) of that number in column B and what they are. so if i had 100 in A1 i would be looking for anything between 80 and 120 in column B. i have tried to use vlookup but vlookup doesnt allow me to type in a range. I was able to do it with if functions but that would require a different column for every row which is inefficient since i have many rows Thank you

The simple way is with a formula like =AND(B1>=A1*0.8,B1<=A1*1.2) in C copied down, to sum you could use =--AND(B1>=A1*0.8,B1<=A1*1.2)
In one formula, something like =SUMPRODUCT(-(B1:B25>=A1:A25*0.8),-(B1:B25<=A1:A25*1.2))would work.

hth

Thank you for your response. I think i need to clarify a little bit. I have attached a spreadsheet to assist. The spreadsheet details the output i am looking for. I used two columns in the original question to make it easier to understand but all of the numbers are in one column (i could always duplicate them in another column if thats the only way to do it). I am looking for a formula to do what i did manually. Also, and i am not sure this is even possible but worth a try, would like to be able to see if any two cells added up would fall in that +/- 20% range and find out what those two are. so for example in the attached sheet, for row 1 you would somehow find out that if you added the values in row 3 and 12, you would be in the range or even rows 11 and 12 would be in the range for row 10.

Thank you

Below is the code that I attached to yoru worksheet. I can't upload files at this point, but you will see the formulas start in C1. This checks against any value in A1. The results are down in the second code section.

Code:

100	92	86	=if(and(b1>$a$1*0.8,b1<$a$1*1.2), true,false)	=if(d1=true,0,if(b1<$a$1,(($a$1*0.8)-b1),(($a$1*1.2)-b1)))
200	190		=if(and(b2>$a$1*0.8,b2<$a$1*1.2), true,false)	=if(d2=true,0,if(b2<$a$1,(($a$1*0.8)-b2),(($a$1*1.2)-b2)))
5	0		=if(and(b3>$a$1*0.8,b3<$a$1*1.2), true,false)	=if(d3=true,0,if(b3<$a$1,(($a$1*0.8)-b3),(($a$1*1.2)-b3)))
10	0		=if(and(b4>$a$1*0.8,b4<$a$1*1.2), true,false)	=if(d4=true,0,if(b4<$a$1,(($a$1*0.8)-b4),(($a$1*1.2)-b4)))
600	0		=if(and(b5>$a$1*0.8,b5<$a$1*1.2), true,false)	=if(d5=true,0,if(b5<$a$1,(($a$1*0.8)-b5),(($a$1*1.2)-b5)))
2000	1900		=if(and(b6>$a$1*0.8,b6<$a$1*1.2), true,false)	=if(d6=true,0,if(b6<$a$1,(($a$1*0.8)-b6),(($a$1*1.2)-b6)))
1900	2000		=if(and(b7>$a$1*0.8,b7<$a$1*1.2), true,false)	=if(d7=true,0,if(b7<$a$1,(($a$1*0.8)-b7),(($a$1*1.2)-b7)))
3000	0		=if(and(b8>$a$1*0.8,b8<$a$1*1.2), true,false)	=if(d8=true,0,if(b8<$a$1,(($a$1*0.8)-b8),(($a$1*1.2)-b8)))
4500	0		=if(and(b9>$a$1*0.8,b9<$a$1*1.2), true,false)	=if(d9=true,0,if(b9<$a$1,(($a$1*0.8)-b9),(($a$1*1.2)-b9)))
190	200		=if(and(b10>$a$1*0.8,b10<$a$1*1.2), true,false)	=if(d10=true,0,if(b10<$a$1,(($a$1*0.8)-b10),(($a$1*1.2)-b10)))
92	100	86	=if(and(b11>$a$1*0.8,b11<$a$1*1.2), true,false)	=if(d11=true,0,if(b11<$a$1,(($a$1*0.8)-b11),(($a$1*1.2)-b11)))
86	100	92	=if(and(b12>$a$1*0.8,b12<$a$1*1.2), true,false)	=if(d12=true,0,if(b12<$a$1,(($a$1*0.8)-b12),(($a$1*1.2)-b12)))

Code:

$100.00	$92.00	$86.00	true	$0.00
$200.00	$190.00		false	-$70.00
$5.00	$0.00		false	$80.00
$10.00	$0.00		false	$80.00
$600.00	$0.00		false	$80.00
$2,000.00	$1,900.00		false	-$1,780.00
$1,900.00	$2,000.00		false	-$1,880.00
$3,000.00	$0.00		false	$80.00
$4,500.00	$0.00		false	$80.00
$190.00	$200.00		false	-$80.00
$92.00	$100.00	$86.00	true	$0.00
$86.00	$100.00	$92.00	true	$0.00

I don't think this can be achieved sensibly with formulae, but a macro will do it:

Code:

Sub find_near()
Dim rCell As Range, rCell2 As Range
For Each rCell In Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
    For Each rCell2 In Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
        If Abs(rCell - rCell2) / rCell <= 0.2 And rCell <> rCell2 Then
            Cells(rCell.Row, Columns.Count).End(xlToLeft)(1, 2) = rCell2
        End If
    Next rCell2
Next rCell
End Sub

Do you know how to use this?

i have no idea how to do what you just did. is that for the first part of my question or the second part?

Thanks, I don't think I am being clear. the spreadsheet i attached is the final output. if you pretend that column A is the only acutal column populated i wanted to see if there was something i could run in order to produce the results in columns B and C. Sorry for the confusion.

Originally Posted by DP978

Below is the code that I attached to yoru worksheet. I can't upload files at this point, but you will see the formulas start in C1. This checks against any value in A1. The results are down in the second code section.

Code:

100	92	86	=if(and(b1>$a$1*0.8,b1<$a$1*1.2), true,false)	=if(d1=true,0,if(b1<$a$1,(($a$1*0.8)-b1),(($a$1*1.2)-b1)))
200	190		=if(and(b2>$a$1*0.8,b2<$a$1*1.2), true,false)	=if(d2=true,0,if(b2<$a$1,(($a$1*0.8)-b2),(($a$1*1.2)-b2)))
5	0		=if(and(b3>$a$1*0.8,b3<$a$1*1.2), true,false)	=if(d3=true,0,if(b3<$a$1,(($a$1*0.8)-b3),(($a$1*1.2)-b3)))
10	0		=if(and(b4>$a$1*0.8,b4<$a$1*1.2), true,false)	=if(d4=true,0,if(b4<$a$1,(($a$1*0.8)-b4),(($a$1*1.2)-b4)))
600	0		=if(and(b5>$a$1*0.8,b5<$a$1*1.2), true,false)	=if(d5=true,0,if(b5<$a$1,(($a$1*0.8)-b5),(($a$1*1.2)-b5)))
2000	1900		=if(and(b6>$a$1*0.8,b6<$a$1*1.2), true,false)	=if(d6=true,0,if(b6<$a$1,(($a$1*0.8)-b6),(($a$1*1.2)-b6)))
1900	2000		=if(and(b7>$a$1*0.8,b7<$a$1*1.2), true,false)	=if(d7=true,0,if(b7<$a$1,(($a$1*0.8)-b7),(($a$1*1.2)-b7)))
3000	0		=if(and(b8>$a$1*0.8,b8<$a$1*1.2), true,false)	=if(d8=true,0,if(b8<$a$1,(($a$1*0.8)-b8),(($a$1*1.2)-b8)))
4500	0		=if(and(b9>$a$1*0.8,b9<$a$1*1.2), true,false)	=if(d9=true,0,if(b9<$a$1,(($a$1*0.8)-b9),(($a$1*1.2)-b9)))
190	200		=if(and(b10>$a$1*0.8,b10<$a$1*1.2), true,false)	=if(d10=true,0,if(b10<$a$1,(($a$1*0.8)-b10),(($a$1*1.2)-b10)))
92	100	86	=if(and(b11>$a$1*0.8,b11<$a$1*1.2), true,false)	=if(d11=true,0,if(b11<$a$1,(($a$1*0.8)-b11),(($a$1*1.2)-b11)))
86	100	92	=if(and(b12>$a$1*0.8,b12<$a$1*1.2), true,false)	=if(d12=true,0,if(b12<$a$1,(($a$1*0.8)-b12),(($a$1*1.2)-b12)))

Code:

$100.00	$92.00	$86.00	true	$0.00
$200.00	$190.00		false	-$70.00
$5.00	$0.00		false	$80.00
$10.00	$0.00		false	$80.00
$600.00	$0.00		false	$80.00
$2,000.00	$1,900.00		false	-$1,780.00
$1,900.00	$2,000.00		false	-$1,880.00
$3,000.00	$0.00		false	$80.00
$4,500.00	$0.00		false	$80.00
$190.00	$200.00		false	-$80.00
$92.00	$100.00	$86.00	true	$0.00
$86.00	$100.00	$92.00	true	$0.00

Hi km,

Please don't quote whole posts (particularly not big ones) - it makes it harder to follow the thread.

As you understand (I didn't at first), simple formulae don't work very well because you have to check every cell against every other - which means a column for every row - not practical. My macro takes every cell and for that cell looks at every other cell and compares them - it's quite simple.

To run it, (clear the sheet so you only have one column of data) right-click the worksheet tab -> View code -> paste the code from above -> put the cursor inside the code ->press F5.

You can close that window and should find your data are magically wonderful - this will take a little while depending on the size of your data set.

Depending on your needs, we can make this much easier to do regularly.

CC

Thanks CC. that works out great. i wish i could understand what you did so i could duplicate or alter the formula...