Forum Discussion
= function
This is basically what I want to do but with more sensitive information that I don't want to post here. So I have different sections that I want th information in F to be in but I need to alter F as I get more/new info so I would like if Side A and Side B automatically updated to correspond to what is in Column F. At the moment I am getting the Ref error. I tried VLookup and match with no success
Hi Aoife,
I agree with Detlef that you need to have a reference column for 'Site'.
I have attached one of the possible solutions to the problem. Essentially, you would need to find an nth match of the Site name in the Animals/Sites table. You can achieve it with a combination of the INDEX, SMALL, IF and ROW functions array entered.
If we add site names in column G, and assume that the Site A range is fixed to B2:B9, you can array enter the following formula in that range (select the range, press F2, then ctrl+shift+enter):
=IFERROR(INDEX($F1:$F8,SMALL(IF($G1:$G8=B1,ROW($F1:$F8)-ROW(INDEX($F1:$F8,1,1))+1),ROW(1:8))),"") .
Then you can copy and paste the formula into the Site B range (D2:D9).
A better solution would be to place the Animals/Site values into an excel table. If the table name were tblAnimals, then the formula in column B would look
=IFERROR(INDEX(tblAnimals[Animal],SMALL(IF(tblAnimals[Site]=B1,ROW(tblAnimals[Animal])-ROW(INDEX(tblAnimals[Site],1,1))+1),ROW(1:8))),"")
In this case, any new items in the lookup table would automatically flow through to the respective site up to the last row of the array range.
Thank you so much for all the help