# Thread: LARGE Formula & Return value from MATCH Thanks: 0 Likes: 0

1. ## LARGE Formula & Return value from MATCH

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)

2. ## Re: LARGE Formula & Return value from MATCH

anyone have any ideas on this

3. ## Re: LARGE Formula & Return value from MATCH

something like this?

ABCDE
1UIDUID
211V1
322V2
433V3
544V4
655V5
7106V6
897V7
988V8
1079V9
11610V10
12
13
14V10

Sheet1

Worksheet Formulas
CellFormula
A14=INDEX(E2:E11,MATCH(LARGE(A2:A11,1),D2:D11,0))

4. ## Re: LARGE Formula & Return value from MATCH

Thank you

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

5. ## Re: LARGE Formula & Return value from MATCH

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

6. ## Re: LARGE Formula & Return value from MATCH

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
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

7. ## Re: LARGE Formula & Return value from MATCH

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

ABCDEFR
1Customer Odds (%) TCV Forecast Date Owner Digital / CSE UID
21A360B1035
32A910B1044
43A610B1031
54A830B1037
65A160B1063
76A590B1021
87A640B1052
98A810B1016
109A880B1048

2H Pipeline

ALTU
1
2B
3
41602
5A

Sheet2

Array Formulas
CellFormula
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)),"")}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

Ex 2

ABCDEFR
1Customer Odds (%) TCV Forecast Date Owner Digital / CSE UID
21A360B1035
32A910B1044
43A6101031
54A8301037
65A1601063
76A5901021
87A6401052
98A810B1016
109A880B1048

2H Pipeline

ALTU
1
2B
3
48802
5A

Sheet2

Array Formulas
CellFormula
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)),"")}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

8. ## Re: LARGE Formula & Return value from MATCH

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

Originally Posted by 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

ABCDEFR
1Customer Odds (%) TCV Forecast Date Owner Digital / CSE UID
21A360B1035
32A910B1044
43A610B1031
54A830B1037
65A160B1063
76A590B1021
87A640B1052
98A810B1016
109A880B1048

2H Pipeline

ALTU
1
2B
3
41602
5A

Sheet2

Array Formulas
CellFormula
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)),"")}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

Ex 2

ABCDEFR
1Customer Odds (%) TCV Forecast Date Owner Digital / CSE UID
21A360B1035
32A910B1044
43A6101031
54A8301037
65A1601063
76A5901021
87A6401052
98A810B1016
109A880B1048

2H Pipeline

ALTU
1
2B
3
48802
5A

Sheet2

Array Formulas
CellFormula
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)),"")}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

9. ## Re: LARGE Formula & Return value from MATCH

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•