View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Mark Lincoln
 
Posts: n/a
Default Returning value based on input data

If you can create a helper column on your sheet with the lookup data,
you can do this:

Assuming entry in Sheet1, A1 and result in Sheet2, A2, with lookup data
in Sheet2 columns A-C and the helper column in D.

In Sheet2, column D enter this next to your first FSC% figu

=IF(AND(Sheet1!$A$1=Sheet2!A1,Sheet1!$A$1<Sheet2! B1),Sheet2!C1,0)

Drag that formula down to your last data row.

in Sheet1, A2 enter:

=MAX(Sheet2!D1:D10)

substituting your helper column range for D1:D10.

When you enter a value in A1, the helper column puts zeros in each of
its rows save the one meeting your criteria. The formula in A2 simply
finds the only nonzero value in the range.