Is it possible to use IF and Vlookup?

[harriet60]

Hello everyone
Hoping someone can help again please!
I am trying to use IF and Vlookup together but can't get it to work.
I have one sheet of data that has results by week (column A = week, column B = Name, Column C = Score Achieved
I then have a second sheet that has column A = Name, column B = Score Achieved, and a single cell with a week number in it (also validated to a list so week number can be changed)
What I am trying to achieve is - If week number is = to single cell number ie: 11, vlookup the name and return the score achieved. Thus, whenever the week number is changed, the vlookup will return the correct data for the week selected.
As always, thanks in advance for any help anyone can give.

 

[Cubist]

Try XLOOKUP.

Book1.xlsx
	A	B	C	D	E	F	G	H	I
1
2
3
4								11
5			Week	Name	Score
6			10	Joe	95%			Joe
7			10	Jim	40%			Jim
8			10	Mark	60%			Mark
9			10	Sally	59%			Sally
10			11	Sue	99%			Sue	99%
11			11	Richard	55%			Richard	55%
12			12	Ben	69%			Ben
13			12	Billy	72%			Billy
14			12	Joanie	86%			Joanie
Sheet8

Cell Formulas
Range	Formula
I6:I14	I6	=XLOOKUP(H4&H6:H14,C6:C14&D6:D14,E6:E14,"")
Dynamic array formulas.

 

[AhoyNC]

Try:

Book1
	A	B	C	D	E	F	G	H	I
1		First Tab					Second Tab
2							Select Week >	11
3	Week	Name	Score					Name	Score
4	10	Joe	95%					Joe
5	10	Jim	40%					Jim
6	10	Mark	60%					Mark
7	10	Sally	59%					Sally
8	11	Sue	99%					Sue	99%
9	11	Richard	55%					Richard	55%
10	12	Ben	69%					Ben
11	12	Billy	72%					Billy
12	12	Joanie	86%					Joanie
Sheet1

Cell Formulas
Range	Formula
I4:I12	I4	=FILTER($C$4:$C$12,($B$4:$B$12=H4)*($A$4:$A$12=$H$2),"")

 

[etaf]

so in your example
i would use index match , but vlookup should work

=index($E$6:$E$14, match($H$4&H6, $C$6:$C$14&$D$6:$D$14,0))
=IFERROR(index($E$6:$E$14, match($H$4&H6, $C$6:$C$14&$D$6:$D$14,0)),"")

Book1
	A	B	C	D	E	F	G	H	I
1
2
3
4								11
5			Week	Name	Score
6			10	Joe	0.95			Joe
7			10	Jim	0.4			Jim
8			10	Mark	0.6			Mark
9			10	Sally	0.59			Sally
10			11	Sue	0.99			Sue	0.99
11			11	Richard	0.55			Richard	0.55
12			12	Ben	0.69			Ben
13			12	Billy	0.72			Billy
14			12	Joanie	0.86			Joanie
Sheet1

Cell Formulas
Range	Formula
I6:I14	I6	=IFERROR(INDEX($E$6:$E$14, MATCH($H$4&H6, $C$6:$C$14&$D$6:$D$14,0)),"")

 

[harriet60]

Try XLOOKUP.

Book1.xlsx
	A	B	C	D	E	F	G	H	I
1
2
3
4								11
5			Week	Name	Score
6			10	Joe	95%			Joe
7			10	Jim	40%			Jim
8			10	Mark	60%			Mark
9			10	Sally	59%			Sally
10			11	Sue	99%			Sue	99%
11			11	Richard	55%			Richard	55%
12			12	Ben	69%			Ben
13			12	Billy	72%			Billy
14			12	Joanie	86%			Joanie
Sheet8

Cell Formulas
Range	Formula
I6:I14	I6	=XLOOKUP(H4&H6:H14,C6:C14&D6:D14,E6:E14,"")
Dynamic array formulas.

Thankyou for your help - it works beautifully

 

[harriet60]

Try:

Book1
	A	B	C	D	E	F	G	H	I
1		First Tab					Second Tab
2							Select Week >	11
3	Week	Name	Score					Name	Score
4	10	Joe	95%					Joe
5	10	Jim	40%					Jim
6	10	Mark	60%					Mark
7	10	Sally	59%					Sally
8	11	Sue	99%					Sue	99%
9	11	Richard	55%					Richard	55%
10	12	Ben	69%					Ben
11	12	Billy	72%					Billy
12	12	Joanie	86%					Joanie
Sheet1

Cell Formulas
Range	Formula
I4:I12	I4	=FILTER($C$4:$C$12,($B$4:$B$12=H4)*($A$4:$A$12=$H$2),"")

Thankyou also for your help = this too works beautifully

 

[harriet60]

so in your example
i would use index match , but vlookup should work

=index($E$6:$E$14, match($H$4&H6, $C$6:$C$14&$D$6:$D$14,0))
=IFERROR(index($E$6:$E$14, match($H$4&H6, $C$6:$C$14&$D$6:$D$14,0)),"")

Book1
	A	B	C	D	E	F	G	H	I
1
2
3
4								11
5			Week	Name	Score
6			10	Joe	0.95			Joe
7			10	Jim	0.4			Jim
8			10	Mark	0.6			Mark
9			10	Sally	0.59			Sally
10			11	Sue	0.99			Sue	0.99
11			11	Richard	0.55			Richard	0.55
12			12	Ben	0.69			Ben
13			12	Billy	0.72			Billy
14			12	Joanie	0.86			Joanie
Sheet1

Cell Formulas
Range	Formula
I6:I14	I6	=IFERROR(INDEX($E$6:$E$14, MATCH($H$4&H6, $C$6:$C$14&$D$6:$D$14,0)),"")

Thank you also for your help - this formula also works beautifully

 

[etaf]

you are welcome