Offset Formula
 

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.

Offset Formula
 

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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"H.Schurch" wrote ...
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.