# Find a number closest to???

#### Champ96

##### New Member
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.

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### Special-K99

##### Well-known Member
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?

##### New Member
Can you post an example of your data?

#### Champ96

##### New Member
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

##### Well-known Member
Am most of the way there...gimme some time

#### Champ96

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

#### Special-K99

##### Well-known Member
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.

Last edited:

#### Special-K99

##### Well-known Member
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

Last edited:

#### Champ96

##### New Member
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

##### Well-known Member
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
FGHIJKLMNOPQRST
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
160,48****5****8***
17**3***55***997***
18***********42***
19******77***4****
20**************5
21******999********
22***************
23***************
Sheet1

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

Hope that helps.

Replies
1
Views
405
Replies
7
Views
313
Replies
4
Views
222
Replies
0
Views
316
Replies
3
Views
218

1,191,274
Messages
5,985,698
Members
439,974
Latest member
sjoerdbosch

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back