Extract Data from three data tabs based on an If, Index, Match Formula

[Lakhvear_Singh]

Hi I have data contained within 3 data tabs. One contains extracted data from a third party for students potential grades in subjects (FFTNEW). The second contains data on students that study a given subject where by if they study it it is identified by a number 1 under that subject column heading (SIMS Data). I had the below formula working when I was able to extract data individually from the third party. (For example FFT50 export would only contain FFT50 data (FFT OLD).  However now the third party only allow extract in one file with different headings on this file (FFT50, FFT20, FFT5) The problem is that not all students study all courses and I'm unable to modify the formula to accommodate this new version of file. I was wondering if any one could help? I would like the function to work as in the third tab (FFT20) I'm unable to upload files on her only images and tries to include column headings to help make it more understandable.

=IFERROR(IF(INDEX('SIMS Data'!$J$2:$AI$196,MATCH('FFT20'!$C3,'SIMS Data'!$B$2:$B$196,0),MATCH('FFT20'!E$2,'SIMS Data'!$J$1:$AI$1,0))=0,"",
VLOOKUP(INT(LEFT(INDEX('FFT20-EXP'!$D$2:$SR$200,MATCH('FFT20'!$C3,'FFT20-EXP'!$D$2:$D$200,0),MATCH('FFT20'!E$1,'FFT20-EXP'!$D$2:$SV$2,0)+10),1)),'Year 10 Targets 2022 Sample.xlsx'!Lookup,1,FALSE)),"")

FFTOLDFFTNEWSIMS DATAFFT20

[#2]

@Lakhvear_Singh 

I'm unable to upload files on her only images and tries to include column headings to help make it more understandable.

It IS possible, however, to post files on OneDrive or GoogleDrive and post a link here granting access to such a file. That would be far more likely to help us help you than the images you've posted here.

And if you could, please supplement that actual file with a more clear explanation of what you want to accomplish. In words. Leave the formula to us. Describe in words what you're trying to retrieve and how to make the connection between the various tabs.

[#3]

@mathetes 

Thanks for your reply. 

https://1drv.ms/x/s!AoD5K4waScfenzZAUw5k6Inwz06g?e=YrGaxk

Here is the link to the file. In the tab called SIMS data there are pupil subject choices. A number 1 under the corresponding subject heading means that they study that subject. In the tab called FFT DATA there are targets for the pupils. However the targets are for every student regardless of whether they study the subject. In the tabs FFT50, FFT20 and FFT5, I would like to take the values from the FFT Data sheet under headings relating to the subject, for example the first subject is Art and Design and the three values for this subject i would like to take the grades from are Art and Design_FFT50_G, Art and Design_FFT20_G and Art and Design_FFT5_G and have the grade copy over for the corresponding subject in sheets FFT50, FFT20 and FFT5 but only if the student studies that course. However i would only like to take the number value and not the +/- after the number. And then repeat this for all other subjects. 

Does this make sense?

[#4]

@Lakhvear_Singh 

Does this make sense?

Yes, but more often No!

You appear to be asking for THREE values to be taken from the FFT Data sheet for each person who has a "1" under Art in SIMS Data yet there's only one cell for each student in FFT50 or FFT20 for Art.

Your description is good, but general. Make it specific to a single student or two.  Could you take ONE student ID, UPN1 for example, and work through  specifically (in words) what values you'd want for UPN1 to appear, from where and into which spot, and why or how those values are selected for that single student. 

By the way, although I understand that you want 3 instead of 3+ (etc), why not just use the numbers in the adjacent column to the first decimal place? That would be more accurate. And easier.

And maybe take your workbook for the sake of solving the question at hand, and eliminate those columns and sheets that are not immediately relevant. Create a simple workbook, in other words, to make it easier to focus; what you have should be saved to apply the formula once created, but at present (at least to this outsider) it's overwhelming with its many many many parts.

[#5]

@mathetes 

That’s right because there will only be one in FFT50 and one in FFT20 as they are two different target grade types. I will upload another file to one drive which was how i used to do it. Hopefully it will become easier to understand and less overwhelming.

The data in this version came in seperate files from the source for FFT Data for each FFT50, FFT20 and FFT5. If you look at the FFT20 sheet it does exactly what i would like but that worked because the export for FFTDATA source had different headings. 

I can’t use the values in the column you suggest as they are of a different grade type that is required. 

[#6]

@Lakhvear_Singh 

https://1drv.ms/x/s!AoD5K4waScfenzZAUw5k6Inwz06g?e=zrbgIO