finding nearest match from an array
 

Hi,
I have an 2 columns, One is an array showing the number of defects /
million .
The other shows its corresponding Sigma Rating.
In another worksheet-2 there exists a similar list. (2 columns)
How can i find "the nearest match" to a particular value (no. of
defects) from the first worksheet in the second,then display the
corrresponding Sigma Rating ( in workseet 2) of the matched value in
worksheet (2).

Thanks !

finding nearest match from an array
 

Hi

Something like
=CHOOSE(MATCH($A2,Sheet2!$A$2,$A$10;1),Sheet2!$B$2 :$B$10)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Arjun Chowdhry" wrote in message
oups.com...
Hi,
I have an 2 columns, One is an array showing the number of defects /
million .
The other shows its corresponding Sigma Rating.
In another worksheet-2 there exists a similar list. (2 columns)
How can i find "the nearest match" to a particular value (no. of
defects) from the first worksheet in the second,then display the
corrresponding Sigma Rating ( in workseet 2) of the matched value in
worksheet (2).

Thanks !

finding nearest match from an array
 

With original datalist to be matched on Sheet1,
ColumnA is number of defects,
Column B has your sigma value.

Exact, same configuration of auxiliary datalist on Sheet2, from A1 to B20.


Try this *array* formula in C1 of Sheet1:

=INDEX(Sheet2!$B$1:$B$20,MATCH(MIN(ABS(Sheet2!$A$1 :$A$20-A1)),ABS(Sheet2!$A$1:$A$20-A1),0))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

*After* CSE entry, copy down as needed.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Arjun Chowdhry" wrote in message
oups.com...
Hi,
I have an 2 columns, One is an array showing the number of defects /
million .
The other shows its corresponding Sigma Rating.
In another worksheet-2 there exists a similar list. (2 columns)
How can i find "the nearest match" to a particular value (no. of
defects) from the first worksheet in the second,then display the
corrresponding Sigma Rating ( in workseet 2) of the matched value in
worksheet (2).

Thanks !