Forum Discussion
Excel formulaqs and functions
I am trying to transfer data from one master file to another sub file(tab) within the same file, using the formula: =INDEX(Master!$A4:$A$2400,MATCH($A$35,Master!$C4:$C$2400,""),1) where col A4 cont...
To lookup on multiple sheets first create named range of all sheets names where to lookup. In attached file it's in sheet Sheets and named as SheetNames
Second, data in all sheets shall be within same ranges, in our case A5:B10.
Formula in Master sheet in cell E2 is
=IFNA(VLOOKUP($A2,INDIRECT("'"&INDEX(SheetNames, MATCH(TRUE,COUNTIFS(INDIRECT("'"&SheetNames&"'!$A$5:$A$10"),$A2)>0,0))&"'!$A5:$B$10"),2,0),"")That is an array formula, i.e. use Ctrl+Shift+Enter to enter it instead of Enter. Drag down after that.
Thx. I'll work on this.