Array formula based on Chip Pearson's lookup data samples

[gregork]

I have an array formula based on Chip Pearson's lookup data samples :

=INDEX('Raw Material'!H3:H500,MATCH(SMALL(ABS('Raw Material'!H3:H500-B83),
{1;2;3}),ABS('Raw Material'!H3:H500-B83),0))

From the value in "B83" it returns 3 nearest match values from my
'RawMaterials' sheet. In the columns next to the 3 values returned I have
left lookup formulas that match the names from 'RawMaterials' to the
values.

1st Problem : It all works well until I get duplicate values then I end up
with 3 values the same instead of the 3 nearest matches.

2nd Problem: Is it possible to have a nearest match formula based on more
than one value? i.e. I would have values in B83 & B84 that would lookup the
ranges 'Raw Material'!H3:H500 & 'Raw Material'!Q3:Q500 respectively.

Any advice would be greatly appreciated.

Regards
gregork

 

[Frank Kabel]

Hi Gregork
try the following if the names are in column I (not fully tested
though)
=INDEX('Raw Material'!H3:H500,MATCH(SMALL(ABS('Raw
Material'!H3:H500-B83+(ROW('Raw Material'!H3:H500)/10000)),
{1;2;3}),ABS('Raw Material'!H3:H500-B83+(ROW('Raw
Material'!H3:H500)/10000)),0))
I added a small value (Row()/10000) to each value to distinguish
identical values from each other. You may have to change this factor
(it should be smaller than your smallest difference between two values
in your list)

Frank