CODEWITHSUNDEEP
excelfunctionexcel-formulaexcel-indirect
AM847
AM847

Reputation: 31

Indirect Function returns #REF

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

Upvotes: 3

Views: 2484

Answers (1)

Dean MacGregor
Dean MacGregor

Reputation: 18701

=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....

Upvotes: 2

Related Questions