If an Input range contains a specific text, then look up that value in another range and return a value from a different column

[RaufR]

Hello there,

I'm looking for some help please after scouring the Net and can't get my head round how to do it.

Basically children in my school are tasked with doing some reading each day and tick off in a grid each day that they have done some reading.
I input into a spreadsheet where they are on this grid each time they hand in their Reading Record. I have produced some reports that tell me in text where they are but I'm also trying to auto populate another report worksheet with "dots" showing the achievement status of each child. (there is an image in the uploaded file)

I hope this makes sense and someone can help me. I have a grasp of basic Excel formulae but no VBA.
If you need any more info please ask.

Thanks in advance

ExcelQuery.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	THIS WILL BE THE LOOKUP AREA ON A WORKSHEET						This is the problem I'm trying to solve, I think it is a INDEX/MATCH/VLOOKUP formula but I can't work it out
2	Date	Reward	Day	Status	LookUp
3	07/09/2021	~ ~ ~	Tue	B1			There will be an Input Range on another worksheet where each day I add values
4	08/09/2021	~ ~ ~	Wed	B2			01-May	02-May	03-May	04-May	05-May	06-May	07-May	08-May	09-May
5	09/09/2021	~ ~ ~	Thu	B3			B3	B6			B12		B16		B22
6	10/09/2021	~ ~ ~	Fri	B4			If a text input of B10, B11, B12 etc. through to B19 is in the highlighted yellow range, then it
7	11/09/2021	~ ~ ~	Sat	B5			will match against the range highlighted in green and return the value in the next column over
8	12/09/2021	~ ~ ~	Sun	B6			i.e. highlighted in red and display that value (i.e. text entry "B10" into the blue cell
9	13/09/2021	~ ~ ~	Mon	B7
10	14/09/2021	~ ~ ~	Tue	B8			If text input B10 - B19 exist in yellow range then put "B10" here è
11	15/09/2021	~ ~ ~	Wed	B9
12	16/09/2021	Bronze Certificate	Thu	B10	B10		I'm then going to do something that says if the blue cell contains B10 then put a lower case L
13	17/09/2021	Bronze Certificate	Fri	B11	B10		into the orange cell which I will format as Wingdings so it displays l
14	18/09/2021	Bronze Certificate	Sat	B12	B10
15	19/09/2021	Bronze Certificate	Sun	B13	B10		If "B10" is in the blue cell then put an "l" here but if not leave blank è
16	20/09/2021	Bronze Certificate	Mon	B14	B10
17	21/09/2021	Bronze Certificate	Tue	B15	B10		Basically, as per screenshot below, I'm trying to automate "dots" appearing on a report worksheet based on
18	22/09/2021	Bronze Certificate	Wed	B16	B10		whether achievement criteria are met e.g. If they have any value between B10-B19 in the input range
19	23/09/2021	Bronze Certificate	Thu	B17	B10		then they have earned a certificate, then if they achieved an input value B20 - B39 then they have earnt a bookmark etc
20	24/09/2021	Bronze Certificate	Fri	B18	B10
21	25/09/2021	Bronze Certificate	Sat	B19	B10
22	26/09/2021	Bronze Bookmark	Sun	B20	B20
23	27/09/2021	Bronze Bookmark	Mon	B21	B20
24	28/09/2021	Bronze Bookmark	Tue	B22	B20
25	29/09/2021	Bronze Bookmark	Wed	B23	B20
26	30/09/2021	Bronze Bookmark	Thu	B24	B20
27	01/10/2021	Bronze Bookmark	Fri	B25	B20
28	02/10/2021	Bronze Bookmark	Sat	B26	B20
29	03/10/2021	Bronze Bookmark	Sun	B27	B20
30	04/10/2021	Bronze Bookmark	Mon	B28	B20
31	05/10/2021	Bronze Bookmark	Tue	B29	B20
32	06/10/2021	Bronze Bookmark	Wed	B30	B20
33	07/10/2021	Bronze Bookmark	Thu	B31	B20
34	08/10/2021	Bronze Bookmark	Fri	B32	B20
Award 2

Cell Formulas
Range	Formula
A3	A3	='C:\Users\Rauf\Documents\Bethany\2021 - 22 6BD\Various\[Home Reading Monitor v3.xlsx]Award'!C2
A4:A34	A4	=A3+1
C3:C34	C3	=TEXT(A3,"Ddd")