Offset Formula

  • Thread starter Thread starter H.Schurch
  • Start date Start date
H

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.
 
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
 
Back
Top