Offset Formula

[H.Schurch]

Dear Experts
I am looking for a formula that would find the lowest 2 numbers in column F
and match the corresponding data in column A and have the result displayed
in cell J5 & J6. Any help greatly appreciated.

 

[David McRitchie]

Hi Hans,
'Would not care to do this in VBA (programming), but as
Worksheet Functions it is fairly straight forward. Well at least
easier than trying to program it.

Lowest =SMALL(F:F,1)
next lowest =SMALL(F:F,2)

to find the row number
=MATCH(SMALL(F:F,1),F:F,0)

to use the row number to identify the corresponding value
in Column A to the entry found in Column F.

J5: =INDEX(A:A,MATCH(SMALL(F:F,1),F:F,0))
J6: =INDEX(A:A,MATCH(SMALL(F:F,2),F:F,0))

You cannot use VLOOKUP because the argument value you
look up is in a later column (F) than the value you seek (A),
so you have to use INDEX and MATCH instead.

More information in HELP and at bottom of
http://www.mvps.org/dmcritchie/excel/vlookup.htm