Find a number closest to???

[Champ96]

Hello

Hope someone can help me with this, since I`ve tried for a while now, but can not get it to do what it is supposed to. I am helpless when it comes to macros, and was hoping that it was possible to do without. Here goes.....
I have a cell F16 that changes value between 0,1-0,2-0,3-0,4. And then I have a cell G16 that changes in values.
What I was hoping is possible, is for some kind of function that use the F16 valuse to search in different columns (ifF16=0,1then search in column h) (0,2=column L, 0,3 = column P, 0,4 = column T). When searching these columns I would like to come up with the numbers that are equal to,closest to but smaller, and closest to and bigger in the cells Q16-17-18.

Is there anyone that understands what I am trying to do here, and can help me??

I would really appreciate it.

T.

 

[Special-K99]

You want to search values in columns H L P and T defined by the value in F16 for the value in G16 ???
Numbers equal, closest smaller, closest bigger should be put in Q16 Q7 Q18.

Is that correct?

 

[XLAddict]

Can you post an example of your data?

 

[Champ96]

Hi
Yes, that is exactly correct, is there any hope? Or am I being too demanding of what I would like to do??

 

[Special-K99]

Am most of the way there...gimme some time

 

[Champ96]

Is there any way I can post the entire spreadsheet in here?? Could maybe be easier...

 

[Special-K99]

May have to stop this. I have this so far.

Having some trouble with the MATCH options -1 +1 (less than greater than)

This works with the number being found
=INDEX(INDIRECT(LOOKUP(RIGHT(F16,1)+0,{1,2,3},{"H1:H1000","L1:L1000","P1:P1000","T1:T1000"})),MATCH(G16,INDIRECT(LOOKUP(RIGHT(F16,1)+0,{1,2,3},{"H1:H1000","L1:L1000","P1:P1000","T1:T1000"})),0),1)

The LOOKUP identifies which column to look at using the last character in F16 and performing an INDIRECT on the column.
There's probably a better way of doing this.

I can't seem to get the MATCH less than/greater than to work
The 0 highlighted above indicates equal, ie find the exact value
The other options -1 and +1 are less than OR EQUAL and greater than OR EQUAL
The OR EQUAL part of the MATCH statement is messing everything up.

 

[Special-K99]

Ok try this

in Q16 (find the number)
=INDEX(INDIRECT(LOOKUP(RIGHT(F16,1)+0,{1,2,3},{"H1:H1000","L1:L1000","P1:P1000","T1:T1000"})),MATCH(G16,INDIRECT(LOOKUP(RIGHT(F16,1)+0,{1,2,3},{"H1:H1000","L1:L1000","P1:P1000","T1:T1000"})),0),1)

in Q17 (less than the number to find)
=INDEX(INDIRECT(LOOKUP(RIGHT(F16,1)+0,{1,2,3},{"H1:H1000","L1:L1000","P1:P1000","T1:T1000"})),MATCH(G16,INDIRECT(LOOKUP(RIGHT(F16,1)+0,{1,2,3},{"H1:H1000","L1:L1000","P1:P1000","T1:T1000"})),0)-1,1)

in Q18 (more than the number to find)
=INDEX(INDIRECT(LOOKUP(RIGHT(F16,1)+0,{1,2,3},{"H1:H1000","L1:L1000","P1:P1000","T1:T1000"})),MATCH(G16,INDIRECT(LOOKUP(RIGHT(F16,1)+0,{1,2,3},{"H1:H1000","L1:L1000","P1:P1000","T1:T1000"})),0)+1,1)

In each of the formulas it find the number exactly, so it needs to exist in the column.
Q17 Q18 just offset the found position by -1 and +1 so it extracts the number before and after.

However...

If the number does not exist in the column you will get an error.
If the number to find is in the first row or the last row of the column you will get an error

 

[Champ96]

Ok, Thanks alot. I will try it and see how it works. Thanks for your effort, really appreciate people that is trying to help.

Thanks again

 

[Snakehips]

Like Special-K99 the below pretty much assumes that there will be suitable numbers to be found.
Error trapping for missing numbers could be added but I feel will necessitate an if statement that will compound the formula somewhat.

Excel Workbook
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
6	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
7	*	*	66	*	*	*	*	*	*	*	*	*	*	*	*
8	*	*	22	*	*	*	99	*	*	*	*	*	*	*	42
9	*	*	3	*	*	*	3	*	*	*	6	*	*	*	6
10	*	*	9	*	*	*	8	*	*	*	8	*	*	*	8
11	*	*	9	*	*	*	33	*	*	*	3	*	*	*	3
12	*	*	55	*	*	*	6	*	*	*	6	*	*	*	6
13	*	*	9	*	*	*	7	*	*	*	7	*	*	*	7
14	*	*	9	*	*	*	2	*	*	*	2	*	*	*	2
15	*	*	*	*	*	*	*	*	*	*	*	*	*	*	66
16	0,4	8	*	*	*	*	5	*	*	*	*	8	*	*	*
17	*	*	3	*	*	*	55	*	*	*	99	7	*	*	*
18	*	*	*	*	*	*	*	*	*	*	*	42	*	*	*
19	*	*	*	*	*	*	77	*	*	*	4	*	*	*	*
20	*	*	*	*	*	*	*	*	*	*	*	*	*	*	5
21	*	*	*	*	*	*	999	*	*	*	*	*	*	*	*
22	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
23	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
Sheet1

Array formulas to be confirmed with Ctrl + Shift + Enter.

Hope that helps.