Forum Discussion

deecoy's avatar
deecoy
Copper Contributor
Nov 27, 2019

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...
excel
Formulas and Functions
Deecoy88's avatar
Deecoy88
Copper Contributor
Dec 09, 2019

SergeiBaklan 

Hi Sergei,

Thanks for your help to date on this formula, but I still am unable to manipulate it.

First, I an unable to add a third sheet C1 with additional source data.  I added C1 to the reference range and am unable to pull the C1 source data over to the Master.  I have attached this file for your review and correction [Copy of Sample Worksheet 6.1]

What am I doing wrong?

 

Secondly, I took the same Copy of Sample worksheet file you sent me and shifted the egg data down to beginning in row 39 instead of row 5, in an effort to fully understand the rules of the formula, and was unable to get the data to the Master.  I've also attached this file [Copy of Sample Worksheet 5.2] for your to review and ask that you correct my formula so that it correctly pulls the data from A1 and B1.  I may not also be entering the array formula correctly in the Master or correctly adding new sheets to  SheetNames. ??????

Fully understanding the use of this formula is very critical to my Excel work, and I  will greatly appreciate your assistance at your earliest convenience.  Thx again.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 09, 2019

Deecoy88 

Brian, as for the second question

In formula both ranges shall start from row 39, you changed only one

=IFNA(VLOOKUP($A2,INDIRECT("'"&INDEX(SheetNames, MATCH(TRUE,COUNTIFS(INDIRECT("'"&SheetNames&"'!$A$39:$A$50"),$A2)>0,0))&"'!$A39:$B$50"),2,0),"")

if to highlight

=IFNA(VLOOKUP($A2,INDIRECT("'"&INDEX(SheetNames, MATCH(TRUE,COUNTIFS(INDIRECT("'"&SheetNames&"'!$A$39:$A$50"),$A2)>0,0))&"'!$A39:$B$50"),2,0),"")

Resources