LARGE Formula & Return value from MATCH

[mtyrrell13]

I have the below Formula using LARGE and right now it returns the largest value in the column, but the Column is a UID field and I am just using it to determine unique value, I want to somehow wrap this formula with and INDEX / MATCH array and first use LARGE to find the largest UID, then using Index / match to match that UID to another field and return the value from that field

=LARGE(IF('2H Pipeline'!E:E=U2,IF('2H Pipeline'!B:B=G5,'2H Pipeline'!C:C)),G4)

 

[mtyrrell13]

anyone have any ideas on this

 

[AlanY]

something like this?

Book1
	A	B	C	D	E
1	UID			UID
2	1			1	V1
3	2			2	V2
4	3			3	V3
5	4			4	V4
6	5			5	V5
7	10			6	V6
8	9			7	V7
9	8			8	V8
10	7			9	V9
11	6			10	V10
12
13
14	V10
Sheet1

Cell Formulas
Range	Formula
A14	=INDEX(E2:E11,MATCH(LARGE(A2:A11,1),D2:D11,0))

 

[mtyrrell13]

Thank you

to do this do I need to duplicate my UID column and have a column A version and a Column D version ?

 

[AlanY]

may be easier if you post a small sample of the data and desired result here.

 

[mtyrrell13]

thank you for your quick reply. I think I follow what your formula is doing and I sorta got it to work but it would only return #NA , I figured that was possibly because it did not have the conditional IF statements in it as well. so I added the IF statements but I get syntax error with it

{=INDEX('2H Pipeline'!A2:R150,MATCH(LARGE(IF('2H Pipeline'!E:E,=U2,IF('2H Pipeline'!B:B=L5,('2H Pipeline'!R:R,1),'2H Pipeline'!C:C,)))),L4-1)}

the goal is
index the entire pipeline sheet
if the user = U2 (column E from the pipeline sheet)
if the status =L5 (column B from the pipeline sheet)
find the largest value in the UID list (column R)
return the value in the TCV column from that same row (column C)
the L4-1 is there to parse through each record that meet the given criteria (the is a COUNTIF formula in L4 that is doing a sum of all records that meet the user / status criteria

here is a sample of the formula that works using only LARGE, the issue with LARGE is it is using the TCV field and that is not always unique

=IFERROR(LARGE(IF('2H Pipeline'!E:E=U2,IF('2H Pipeline'!B:B=L5,'2H Pipeline'!C:C)),L4-1),"")

I am not sure how to post the table like you have to show you a sample of the data from the pipeline sheet but here it is in txt format

Customer = Column A
Odds (%) = Column B
TCV = Column C
Forecast Date =Column D
Owner =Column E
Digital / CSE =Column F
Description = Column G
Type =Column H
SC Number =Column I
Opportunity Tower =Column J
Owner Column =Column K
Service Contract # Column L
Start Date Column =Column M
End Date =Column N
Active =Column O
Link =Column P
SC # =Column Q
UID =Column R
Customer name Stretch $200,000.00 19-Aug-19 User name User assist Deal detail Channel SC Link Tower Market Segment Number Start End Yes Link F4-YD94FLR# 202,170.00

 

[AlanY]

see if this is what you're after, btw I've reduced the ranges for testing but try not to reference the whole column as if will slow thing down.

Ex1

Book1
	A	B	C	D	E	F	R
1	Customer	Odds (%)	TCV	Forecast Date	Owner	Digital / CSE	UID
2	1	A	360		B		1035
3	2	A	910		B		1044
4	3	A	610		B		1031
5	4	A	830		B		1037
6	5	A	160		B		1063
7	6	A	590		B		1021
8	7	A	640		B		1052
9	8	A	810		B		1016
10	9	A	880		B		1048
2H Pipeline

Book1
	A	L	T	U
1
2				B
3
4	160	2
5		A
Sheet2

Cell Formulas
Range	Formula
A4	{=IFERROR(INDEX('2H Pipeline'!C2:C11,MATCH(LARGE(IF('2H Pipeline'!E2:E11=U2,IF('2H Pipeline'!B2:B11=L5,'2H Pipeline'!R2:R11)),L4-1),'2H Pipeline'!R2:R10,0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Ex 2

Book1
	A	B	C	D	E	F	R
1	Customer	Odds (%)	TCV	Forecast Date	Owner	Digital / CSE	UID
2	1	A	360		B		1035
3	2	A	910		B		1044
4	3	A	610				1031
5	4	A	830				1037
6	5	A	160				1063
7	6	A	590				1021
8	7	A	640				1052
9	8	A	810		B		1016
10	9	A	880		B		1048
2H Pipeline

Book1
	A	L	T	U
1
2				B
3
4	880	2
5		A
Sheet2

Cell Formulas
Range	Formula
A4	{=IFERROR(INDEX('2H Pipeline'!C2:C11,MATCH(LARGE(IF('2H Pipeline'!E2:E11=U2,IF('2H Pipeline'!B2:B11=L5,'2H Pipeline'!R2:R11)),L4-1),'2H Pipeline'!R2:R10,0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[mtyrrell13]

Thank you I tried both examples and both work, however I tested changing multiple lines having the same TCV and it still has the same issue I had when using only "Large" based on the TCV column.

I originally added the UID column to first find the largest UID that meet the criteria. Then return the TCV value from that row. Your example seems to find the largest UID but is returning the first match on TCV, and I can not figure out why because I made two TCV's identical with different users and a user that did not even match the IF criteria

see if this is what you're after, btw I've reduced the ranges for testing but try not to reference the whole column as if will slow thing down.

Ex1

Book1
	A	B	C	D	E	F	R
1	Customer	Odds (%)	TCV	Forecast Date	Owner	Digital / CSE	UID
2	1	A	360		B		1035
3	2	A	910		B		1044
4	3	A	610		B		1031
5	4	A	830		B		1037
6	5	A	160		B		1063
7	6	A	590		B		1021
8	7	A	640		B		1052
9	8	A	810		B		1016
10	9	A	880		B		1048
2H Pipeline

Book1
	A	L	T	U
1
2				B
3
4	160	2
5		A
Sheet2

Cell Formulas
Range	Formula
A4	{=IFERROR(INDEX('2H Pipeline'!C2:C11,MATCH(LARGE(IF('2H Pipeline'!E2:E11=U2,IF('2H Pipeline'!B2:B11=L5,'2H Pipeline'!R2:R11)),L4-1),'2H Pipeline'!R2:R10,0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Ex 2

Book1
	A	B	C	D	E	F	R
1	Customer	Odds (%)	TCV	Forecast Date	Owner	Digital / CSE	UID
2	1	A	360		B		1035
3	2	A	910		B		1044
4	3	A	610				1031
5	4	A	830				1037
6	5	A	160				1063
7	6	A	590				1021
8	7	A	640				1052
9	8	A	810		B		1016
10	9	A	880		B		1048
2H Pipeline

Book1
	A	L	T	U
1
2				B
3
4	880	2
5		A
Sheet2

Cell Formulas
Range	Formula
A4	{=IFERROR(INDEX('2H Pipeline'!C2:C11,MATCH(LARGE(IF('2H Pipeline'!E2:E11=U2,IF('2H Pipeline'!B2:B11=L5,'2H Pipeline'!R2:R11)),L4-1),'2H Pipeline'!R2:R10,0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[mtyrrell13]

I think I may know what the issue is, testing another change, stand by