If statement with vlookup link to multiple sheets

[Rechie]

I'm computing equivalent % of a school subject base on Perfect score %.
i.e If A1=100 (perfect score); A2=96(Actual score) of sheet 1, then in B2 it
will shows equivalent of A2 % from Sheet2=100% perfect score. I have 19
separate sheet for each Equivalent score % of 10 up to 100 perfect score.
Example, Sheet 2=100 perfect score, Sheet 3=95 perfect Score, etc. Which
means I have to include all this 19 sheet in my formula because it will
calculate base on whatever I input in A1.

Sheet 1
(A1) Perf_ Score = 100 (or 95, etc. Input data_variable)
(A2) Actual Score =96
(B2) Equiv= 98% (fr Sheet 2)

Example of Sheet 2= 100 perfect score
Score Equiv %
96 98
97 98
98 99
99 99
100 100

Thanks for any help you can extend...Rechie

 

[Jacob Skaria]

Copy the below formula to B2. Please note that this is an array formula.
Within the cell in edit mode (F2) paste this formula and press
Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you
can notice the curly braces at both ends like "{=<formula>}"

=VLOOKUP(A2,INDIRECT("'"&INDEX($J$1:$J$19,MATCH(TRUE,COUNTIF(INDIRECT("'"&$J$1:$J$19&"'!A:A"),A2)>0,0))&"'!A:B"),2,0)

Points to be noted

--$J$1:$J$19 is a range in the current sheet in which all the 19 sheet names
are entered (without blanks and spelled ****exactly*** same as the sheet
name)..This can even be a named range...If the names do not match

--A:B is the lookup array in each sheet...If you have only five entries in
each sheet in in ColA/B then you can modify this to A1:B5



If this post helps click Yes

 

[Rechie]

Hi Jacob,

Thanks... The formula is perfectly working. In the formula, I noticed that
range A1 is not included. This is important as this is the basis of the
computation of % equivalent of the actual score of the student. Because the
Number of items test or perfect score that we have given to students always
varies. Sometimes we are giving them 100, 95 or 40, etc. as total number of
items. If I'm giving them up to 80 items only (perfect score) then I will
input 80 in A1. The sheet name "PerfectScore=80" will be called in the
formula. Each sheet has different equivalent of %.

Rechie

 

[Jacob Skaria]

Do you mean the below; which will pick the right sheet ...I didnt know you
have named your sheets this way and hence my previous response..

PerfectScore=100
PerfectScore=95
PerfectScore=90
....and so on

=VLOOKUP(A2,INDIRECT("'PerfectScore=" & A1 & "'!A:B"),2,0)

If this post helps click Yes

 

[Rechie]

Hello Jacob,

Fantastic ! Your formula is what I need in my worksheet.
It's really a big help. You're doing a great job guys.

Thank you very much.

Regards,
Rechie