Find nearest value in a column based on a criteria

[uttamsaxena]

I have a table to find a data value. In one cell A1 a number between 1 to 15 is selected and in another cell A2 a value is typed. Suppose A1 = 14 and A2 = 176482.

Now the objective is to find the exact or next higher value as given in A2 in the column with top row value 14 form the table. Here Column with header 14 don't have exact value as in A2. So It must output next higher value in column 14 ie 177400 in output cell A3. Can one help with a formula for cell A3.

1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
19900	1700	22400	27200	29800	36800	44900	47600	53100	56100	67700	123100	131100	144200	171400
20500	1800	23100	28000	30700	37900	46200	49000	54700	57800	69700	126800	135000	148500	176500
21100	1900	23800	28800	31600	39000	47600	50500	56300	59500	71800	130600	139100	153000	181800
21700	2000	24500	29700	32500	40200	49000	52000	58000	61300	74000	134500	143300	157600	187300
22400	2100	25200	30600	33500	41400	50500	53600	59700	63100	76200	138500	147600	162300	192900
23100	2200	26000	31500	34500	42600	52000	55200	61500	65000	78500	142700	152000	167200	198700
23800	2300	26800	32400	35500	43900	53600	56900	63300	67000	80900	147000	156600	172200	204700
24500	2400	27600	33400	36600	45200	55200	58600	65200	69000	83300	151400	161300	177400	210800
25200	2500	28400	34400	37700	46600	56900	60400	67200	71100	85800	155900	166100	182700
26000	2600	29300	35400	38800	48000	58600	62200	69200	73200	88400	160600	171100	188200
26800	2700	30200	36500	40000	49400	60400	64100	71300	75400	91100	165400	176200	193800
27600	2800	31100	37600	41200	50900	62200	66000	73400	77700	93800	170400	181500	199600
28400	2900	32000	38700	42400	52400	64100	68000	75600	80000	96600	175500	186900	205600
29300	3000	33000	39900	43700	54000	66000	70000	77900	82400	99500	180800	192500	211800
30200	3100	34000	41100	45000	55600	68000	72100	80200	84900	102500	186200	198300	218200
31100	3200	35000	42300	46400	57300	70000	74300	82600	87400	105600	191800	204200
32000	3300	36100	43600	47800	59000	72100	76500	85100	90000	108800	197600	210300
33000	3400	37200	44900	49200	60800	74300	78800	87700	92700	112100	203500	216600
34000	3500	38300	46200	50700	62600	76500	81200	90300	95500	115500	209600
35000	3600	39400	47600	52200	64500	78800	83600	93000	98400	119000	215900
36100	3700	40600	49000	53800	66400	81200	86100	95800	101400	122600
37200	3800	41800	50500	55400	68400	83600	88700	98700	104400	126300
38300	3900	43100	52000	57100	70500	86100	91400	101700	107500	130100
39400	4000	44400	53600	58800	72600	88700	94100	104800	110700	134000
40600	4100	45700	55200	60600	74800	91400	96900	107900	114000	138000
41800	4200	47100	56900	62400	77000	94100	99800	111100	117400	142100
43100	4300	48500	58600	64300	79300	96900	102800	114400	120900	146400
44400	4400	50000	60400	66200	81700	99800	105900	117800	124500	150800
45700	4500	51500	62200	68200	84200	102800	109100	121300	128200	155300
47100	4600	53000	64100	70200	86700	105900	112400	124900	132000	160000
48500	4700	54600	66000	72300	89300	109100	115800	128600	136000	164800
50000	4800	56200	68000	74500	92000	112400	119300	132500	140100	169700
51500	4900	57900	70000	76700	94800	115800	122900	136500	144300	174800
53000	5000	59600	72100	79000	97600	119300	126600	140600	148600	180000
54600	5200	61400	74300	81400	100500	122900	130400	144800	153100	185400
56200	5400	63200	76500	83800	103500	126600	134300	149100	157700	191000
57900	5600	65100	78800	86300	106600	130400	138300	153600	162400	196700
59600	5800	67100	81200	88900	109800	134300	142400	158200	167300	202600
61400	6000	69100	83600	91600	113100	138300	146700	162900	172300	208700
63200	6200	71200	86100	94300	116500	142400	151100	167800	177500

<tbody>
</tbody>

 

[Swapnil Shah]

hi,

Considering your data is in range (A5:O44) use following array formula in Cell A3

=MIN(IF((((A4:O4=$A$1)*(A5:O44)<>0)+((A5:O44)>=$A$2))=2,(A5:O44),"False"))

Please Press Ctrl+Shift+Enter and not only Enter.

One point to highlight is there is the output comes as Zero (0) , please note that there is no value in the provided column which matches given criteria.

Thanks

Swapnil Shah

 

[Aladin Akyurek]

I have a table to find a data value. In one cell A1 a number between 1 to 15 is selected and in another cell A2 a value is typed. Suppose A1 = 14 and A2 = 176482.

Now the objective is to find the exact or next higher value as given in A2 in the column with top row value 14 form the table. Here Column with header 14 don't have exact value as in A2. So It must output next higher value in column 14 ie 177400 in output cell A3. Can one help with a formula for cell A3.

[...]

Let A1:O41 of Sheet1 house the data.

A1 of Sheet2 houses a relative column indicator like 14.

A2 of Sheet2 houses a look up value like 176482.

In A3 of Sheet2 enter:

=INDEX(Sheet1!$A$2:$O$41,MATCH(A2,INDEX(Sheet1!$A$2:$O$41,0,A1),1)+(VLOOKUP(A2,INDEX(Sheet1!$A$1:$O$41,0,MATCH(A1,INDEX(Sheet1!$A$1:$O$41,1,0),0)),1,1) < A2),A1)

 

[uttamsaxena]

hi,

Considering your data is in range (A5:O44) use following array formula in Cell A3

=MIN(IF((((A4:O4=$A$1)*(A5:O44)<>0)+((A5:O44)>=$A$2))=2,(A5:O44),"False"))

Please Press Ctrl+Shift+Enter and not only Enter.

One point to highlight is there is the output comes as Zero (0) , please note that there is no value in the provided column which matches given criteria.

Thanks

Swapnil Shah

Thanks for your help but somehow it is returning zero with the given example.

 

[Aladin Akyurek]

See post #3 ...

 

[uttamsaxena]

Let A1:O41 of Sheet1 house the data.

A1 of Sheet2 houses a relative column indicator like 14.

A2 of Sheet2 houses a look up value like 176482.

In A3 of Sheet2 enter:

=INDEX(Sheet1!$A$2:$O$41,MATCH(A2,INDEX(Sheet1!$A$2:$O$41,0,A1),1)+(VLOOKUP(A2,INDEX(Sheet1!$A$1:$O$41,0,MATCH(A1,INDEX(Sheet1!$A$1:$O$41,1,0),0)),1,1) < A2),A1)

Thanks Aladin, it is working perfectly.

 

[Aladin Akyurek]

Thanks Aladin, it is working perfectly.

You are welcome.

 

[Swapnil Shah]

Thanks for your help but somehow it is returning zero with the given example.

Did you Press Ctrl+Shift+Enter post entering the formula ?... As I did check and found to be working ...