Which Formula?

[MarkAndrews]

Bit of a complicated one here

Basically, what I need to do is have V22 (Coloured in Red) look at V20 (Coloured in Green) and match the nearest result in the table contained in Y:Z

Currently showing 240.5 in V20, therefore the result in V22 should be 1.00 (Nearest match from the table in Y:Z)

HELP!!!!!!!

TIA

India Capacity Plan.xls
	U	V	W	X	Y	Z
1	ENWI-TEL				105	0.25
2		Hours	1		210	0.50
3	Days	5			315	0.75
4	Sale&Purchase	420	84.00		420	1.00
5	Remortgage	4806	961.20		525	1.25
6	Total	5231			630	1.50
7					735	1.75
8	Sale&Purchase	0.99	83.16		840	2.00
9	Remortgage	0.00	0		945	2.25
10					1050	2.50
11	Sale&PurchaseAgentsReq	1.39			1155	2.75
12	RemortgageAgentsReq	0			1260	3.00
13	ENWITotalAgents	1.39			1365	3.25
14					1470	3.50
15	Schedulling				1575	3.75
16		Hours			1680	4.00
17	SchedullingTotal	3250			1785	4.25
18	Daily	162.5			1890	4.50
19	AverageTimePerDoc	1.48	Hours		1995	4.75
20	AverageDailyTime(Mins)	240.5			2100	5.00
21					2205	5.25
22	SchedullingAgentsRequired				2310	5.50
23					2415	5.75
24					2520	6.00
Analysis

 

[Domenic]

Shouldn't the closest value be 210, with the corresponding value being 0.50? If so, try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=INDEX($Y$1:$Y$24,MATCH(MIN(ABS($X$1:$X$24-V20)),ABS($X$1:$X$24-V20),0))

Hope this helps!

 

[RalphA]

How about:
=VLOOKUP(V20,Y1:Z24,2)
It gives 0.5 for V20=240.5

 

[Domenic]

Just to be clear, if V20 contains 300, Ralph's formula will return 0.50, whereas my formula will return 0.75. Not sure which one you're looking for...

 

[MarkAndrews]

Hi Guys!

Thanks for taking the time to look at my post

What I need is this -

If V20 = 240.5, I need the formula in V22 to look at the list contained in Y:Z and return the value which is closest to V20, from column Z

So if V20 = 240.5, the result in V22 would be 0.50, as this number is between 210 (Y2) & 315 (Y3), as its closer to 210, the result would need to be 0.50

Hope this is a little clearer?

Mark

 

[Domenic]

Two solutions have been offered. Have you tried any of them? Note that in my previous post I tried to clarify the difference between the two solutions.

 

[MarkAndrews]

Two solutions have been offered. Have you tried any of them? Note that in my previous post I tried to clarify the difference between the two solutions.

Domenic - I tried your, but it returned the wrong value, it returned the values in Y, I need the values in Z

Ralph's appears to be working OK?

Boss appears to be happy, so I guess its solved?

Thanks again