10 sheet v-lookup

[Soccerx12]

I am trying to run a v-look up against the previous/all other sheets in a file.

The value I am searching for starts in A7 in each worksheet. The numbers I want to pull start in E7 in each worksheet. Values do repeat but I only need to pull it once.

 

[oldbrewer]

not clear

 

[SpillerBD]

( No the formula below WILL NOT WORK, but may help in conceptualizing the goal)
The concept would be like...
=VLOOKUP(LookupValue,Sheet1:Sheet10!$A$7:$E$99,5,FALSE)

or a 3D range for applying a VLOOKUP or other lookup function.

 

[L. Howard]

See if this works for you, I believe I have translation from your formula to the array formula correct.

Where MySheets is a list of the 10 sheets names and then named MySheets in the name box.

The red and blue fonts are for reference from your formula to the array formula.

Use Ctrl + Shift + Enter

=VLOOKUP([COLOR="#FF0000"]LookupValue[/COLOR],[COLOR="#0000FF"]Sheet1:Sheet10![/COLOR]$A$7:$E$99,5,FALSE)

=VLOOKUP([COLOR="#FF0000"]B1[/COLOR],INDIRECT("'"&INDEX([COLOR="#0000FF"]MySheets[/COLOR],MATCH(1,--(COUNTIF(INDIRECT("'"&[COLOR="#0000FF"]MySheets[/COLOR]&"'!A7:A99"),[COLOR="#FF0000"]B1[/COLOR])>0),0))&"'!A7:E99"),5,0)

Howard