3

I am trying to use a match/indirect function to find a specific value for a range of cells on a separate sheet in excel. The A3 value is the sheet number that the function is referencing and the '1' again the sheet it is referencing.

The F1:F4000 is the range from the other sheet and the >7.5 is because I am looking for the first value above 7.5 in that range. Here is what I have so far:

=MATCH(TRUE,INDIRECT($A3&"!'1'!F1:F4000">7.5,0),

Any help would be greatly appreciated

hjpotter92
  • 78,589
  • 36
  • 144
  • 183
AM847
  • 31
  • 1
  • 1
  • 6
  • You can't have two references to the sheet. You have A3 and '1' - what's the actual sheet name - what's in A3? – barry houdini Aug 05 '13 at 15:07
  • The actual sheet name is 1, and in cell A3 it is simply 1. It is referencing back to Sheet 1. I realize now that I cannot double reference the sheet but what about this: '=MATCH(TRUE,INDIRECT($A3&!$F1:$F4000>7.5),0),0)' – AM847 Aug 05 '13 at 15:11
  • 5
    Try this `=MATCH(TRUE,INDIRECT("'"&$A3&"'!F1:F4000")>7.5,0)` - formula needs to be "array entered" with CTRL+SHIFT+ENTER – barry houdini Aug 05 '13 at 15:15
  • 2
    @barryhoudini: Write up an answer... – Werner Aug 09 '13 at 05:42

1 Answers1

2

=MATCH(TRUE,INDIRECT("'"&$A3&"'!F1:F4000")>7.5,0)

As this is an array function, it must be entered with CTRL-SHIFT-ENTER. I "stole" @barry hourdini's answer because this is 2 and a half months old and still on unanswered section....

Dean MacGregor
  • 11,847
  • 9
  • 34
  • 72