Combine Formulas

[VinceF]

Greetings Experts and Thank You in advance.
I'm trying to combine these two formulas without much sucess, assistance appreciated.

In E5 you'll see this formula to match the players in the column with the value from the other sheet "Player Quota History"
IF(Main!$B5="","",INDEX('Player Quota History'!D5:D12,MATCH(Main!$B5,'Player Quota History'!B5:B12,0)))
In G5 this formula reports the last value in the specific row.
LOOKUP(2,1/('Player Quota History'!D5:H5<>""),'Player Quota History'!D5:H5)

I'm going to attempt to show both sheets using Xl2bb

VinceF
Excel 2016

Sample.xlsx
	B	C	D	E	F	G	H
3
4
5	Larry			4		1
6	Moe
7	Curly
8	Pete
9	Sam
10	Joe
11	Mike
12	Bob
13
14
Main

Sample.xlsx
	B	C	D	E	F	G	H	I
3
4
5	Bob		3	4	1
6	Curly		2	1
7	Joe		5	7
8	Larry		4	6
9	Mike		1
10	Moe		9
11	Pete		5	8
12	Sam		2	7
13
14
15
16
17
Player Quota History

 

[jtakw]

Hi,

I'm unsure of what you're trying to achieve as the result(s), are you trying to get the Last score for each player?
Or, both the 1st and Last scores into 2 separate columns ?

 

[VinceF]

I'm sure my explanation was a bit confusing, I'll try to do better here.

This formula matches players name in a column and then reports a value from another column.
=IF(Main!$B5="","",INDEX('Player Quota History'!D5:D12,MATCH(Main!$B5,'Player Quota History'!B5:B12,0)))

This formula looks at a row and reports the last value in that row.
=LOOKUP(2,1/('Player Quota History'!D5:H5<>""),'Player Quota History'!D5:H5)

Both formulas work as needed but independently. I'd like to combine these two formulas into 1 cell to look something like this.
=IF(Main!$B5="","",INDEX('Player Quota History'!D5:D12,MATCH(Main!$B5,'Player Quota History'!B5:B12,0)))=LOOKUP(2,1/('Player Quota History'!D5:H5<>""),'Player Quota History'!D5:H5)

When I try to combine the formulas, it ask me to correct the formula to this... It actually puts an * before the LOOKUP function...I'm not sure what the * means or what it does but never the less it's not working as it should.
=IF(Main!$C12="","",INDEX('Player Quota History'!E4:E33,MATCH(Main!$C12,'Player Quota History'!C4:C33,0)*LOOKUP(2,1/('Player Quota History'!F12:AK12<>""),'Player Quota History'!F12:AK12)))


I hope that this helps.

vinceF

 

[jtakw]

No, it's still unclear, what are the results you're looking for, for each player?

Since the 2 formulas produce completely different results, why are you trying to combine them?

 

[VinceF]

With my limited Excel skills, this is a tough one to explain.

I tried to combine the formulas in "Main" H12

Main Sheet
C12 has the participating players name, participants may change from week to week therefore the names are not static.
G12 reports the players Quota
H12 I want to be able to adjust G12's quota number based on if that player did not win on any given week. If the player has not won I want to adjust his Quota number in G12.
This formula matches the player in C12 with the player listed in the "Player Quota Sheet" from column C
=IF(Main!$C12="","",INDEX('Player Quota History'!E4:E33,MATCH(Main!$C12,'Player Quota History'!C4:C33,0)

To keep track of the players wins or losses I have a sheet listing every players names that will play in the game called, "Player Quota History". On that sheet the players names are in Column C4:C57
In column E4:e57 reports the individual players Quota.
In Row E4:AK4 it automatically adjust the players quota down by 1 if that player did not win that week.
This formula reports the last cell with a value in it. =LOOKUP(2,1/('Player Quota History'!D5:H5<>""),'Player Quota History'!D5:H5)

Does this help?
VinceF

Master Skins-Stableford Templet 3-11-22.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
10
11	TEAM REQ	PLAYER	INDEX	HDCP	STROKES	QUOTA	QUOTA ADJ		SKINS	FRONT
12	No	Tucker	9.0	12	12	23	#REF!		Yes
13
14
15
16
17
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B11	Expression	=$B$12="no"	text	NO
B11	Expression	=$B$12="yes"	text	NO
C11	Expression	=ISTEXT($C$12)	text	NO
C11	Expression	=$B$12="yes"	text	NO
B11	Expression	=AND($AH$4="place", $B$4="skins & stableford")	text	NO
B11	Expression	=AND($AH$4="point", $B$4="skins & stableford")	text	NO
B11	Expression	=AND($B$4="skins", ISNUMBER($E$4))	text	NO
B11	Expression	=AND($AH$4="point", $B$4="stableford")	text	NO
B11	Expression	=AND($AH$4="place", $B$4="stableford")	text	NO
C11	Expression	=$B$12="no"	text	NO
S12:S51	Expression	=AND($B$7="half",'Half Stroke Skins'!$AH5=1)	text	NO
S12:S51	Expression	=AND($B$7="full",' Full Stroke Skins'!$AH5=1)	text	NO
L12:R12	Expression	=AND($B$7="half",'Half Stroke Skins'!AA$5=1)	text	NO
L12:R12	Expression	=AND($B$7="full",' Full Stroke Skins'!AA$5=1)	text	NO
R13:R51	Expression	=AND($B$7="half",'Half Stroke Skins'!$AG6=1)	text	NO
R13:R51	Expression	=AND($B$7="full",' Full Stroke Skins'!$AG6=1)	text	NO
Q13:Q51	Expression	=AND($B$7="half",'Half Stroke Skins'!$AF6=1)	text	NO
Q13:Q51	Expression	=AND($B$7="full",' Full Stroke Skins'!$AF6=1)	text	NO
P13:P51	Expression	=AND($B$7="half",'Half Stroke Skins'!$AE6=1)	text	NO
P13:P51	Expression	=AND($B$7="full",' Full Stroke Skins'!$AE6=1)	text	NO
O13:O51	Expression	=AND($B$7="half",'Half Stroke Skins'!$AD6=1)	text	NO
O13:O51	Expression	=AND($B$7="full",' Full Stroke Skins'!$AD6=1)	text	NO
N13:N51	Expression	=AND($B$7="half",'Half Stroke Skins'!$AC6=1)	text	NO
N13:N51	Expression	=AND($B$7="full",' Full Stroke Skins'!$AC6=1)	text	NO
M13:M51	Expression	=AND($B$7="half",'Half Stroke Skins'!$AB6=1)	text	NO
M13:M51	Expression	=AND($B$7="full",' Full Stroke Skins'!$AB6=1)	text	NO
L13:L51	Expression	=AND($B$7="half",'Half Stroke Skins'!$AA6=1)	text	NO
L13:L51	Expression	=AND($B$7="full",' Full Stroke Skins'!$AA6=1)	text	NO
K12:K51	Expression	=AND($B$7="half",'Half Stroke Skins'!$Z5=1)	text	NO
K12:K51	Expression	=AND($B$7="full",' Full Stroke Skins'!$Z5=1)	text	NO
J11	Expression	=($B$4="skins")	text	NO
J11	Expression	=($B$4="stableford")	text	NO
J11	Expression	=ISTEXT($J$12:$J$51)	text	NO
J11	Expression	=ISNUMBER($D$12:$D$51)	text	NO
J11	Expression	=$B$4="Stableford"	text	YES
D11	Expression	=ISNUMBER($D$12)	text	NO
D11	Expression	=ISTEXT($C$12)	text	NO
I13:I51	Cell Value	contains "No"	text	NO
I12	Cell Value	contains "No"	text	NO

Master Skins-Stableford Templet 3-11-22.xlsm
	B	C	D	E	F	G	H	I	J
2				5/1
3	#	PLAYER		Start	(W/L)	Quota	(W/L)	Quota	(W/L)
4	1	Altman	Adam	10	l	9	l	8
5	2	Beasley	Arlen	28	l	27
6	3	Calgwell	Gary	24
7	4	Curtis	Jim	22
8	5	Delong	Joe	24
9	6	Draggoo	Tylor	32
10	7	Draggoo	Kraig	22
11	8	Edgar	Jeff	26	l	25	l	24
12	9	Finazzo	Vince	3	l	2
13	10	Hanna	Marty	33
Player Quota History

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E4	Expression	=ISTEXT($E$40)	text	NO
E4	Expression	=ISNUMBER(#REF!)	text	NO
E4	Expression	="if(and($B$2=""skins"",$E$2=ismumber)"	text	NO
E4	Expression	=ISTEXT($D$35)	text	NO