Need formula for If and Match, I think, for referencing cells in different sheets

[Goddess]

Hi!

Basically, I have 3 sheets in one workbook.

Sheet 1 is the data that I will use as a list for data validation.
Sheet 2 is a list of questionnaire, where we will score them based on the replies
Sheet 3 is the scoring page, where the final score will be tabulated

Based on my example above, Sheet 1 will have the scores manually keyed in for each person. On Sheet 2 (B1), I will select the list of names using data validation, referring back to Sheet 1 (B2:B12).

Finally, on Sheet 3, it will automatically shows that Mike's score is 3, Excellent (A2:B2) based on his result in Sheet 1, which is 90. I need the formula for Sheet 3, A2 and B2.

Thanks!
Irene

 

[maabadi]

After add Data validation, Try this at Sheet3:

Book1
	A	B	C
1	Score
2	3	Excellent
3
Sheet3

Cell Formulas
Range	Formula
A2	A2	=LOOKUP(INDEX(Sheet1!$C$2:$C$12,MATCH(Sheet2!$B1,Sheet1!$B$2:$B$12,FALSE)),{0,69,80},{1,2,3})
B2	B2	=CHOOSE(A2,"Poor","Good","Excellent")

 

[Goddess]

After add Data validation, Try this at Sheet3:

Book1
	A	B	C
1	Score
2	3	Excellent
3
Sheet3

Cell Formulas
Range	Formula
A2	A2	=LOOKUP(INDEX(Sheet1!$C$2:$C$12,MATCH(Sheet2!$B1,Sheet1!$B$2:$B$12,FALSE)),{0,69,80},{1,2,3})
B2	B2	=CHOOSE(A2,"Poor","Good","Excellent")

Hi Maabadi,

Thanks! Both formula works!

By the way, for Sheet 3 formula for B2, if I want to base on the scoring of <69, 70-79 & >80, what's the formula to use? I can't figure out the '70-79' when using the IFS formula.

Thanks again!

 

[maabadi]

I don't understand what you want do exactly. when your Score is between 70-79, you get 2 at A2 and Good at B2. What else you want?
Please give example & Desired Result.

 

[Goddess]

I don't understand what you want do exactly. when your Score is between 70-79, you get 2 at A2 and Good at B2. What else you want?
Please give example & Desired Result.

Hey!

I got it figured out already. Thanks!

 

[maabadi]

You're Welcome & Thanks for Follow-up.