largest three numbers - display cell to left of it

Jordie

Board Regular
Joined
Feb 3, 2005
Messages
58
hi

I have a formula that works for displaying the largest defect which is as follows:
{=LARGE($D$2:$D$325*IF($C$2:$C$325=F2,1,0),1)}

It works well, but I also want to display the cell that is one column to the left of the displayed number. Any thoughts?

jordie
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,119
Jordie said:
hi

I have a formula that works for displaying the largest defect which is as follows:
{=LARGE($D$2:$D$325*IF($C$2:$C$325=F2,1,0),1)}

It works well, but I also want to display the cell that is one column to the left of the displayed number. Any thoughts?

jordie
Care to post 10 rows from the ranges of interest?
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
9,999
Maybe I'm missing something but isn't the number being displayed coming from D2:D325? If so then the cell one column to the left is coming from C2:C325, which is matching F2. So can't you just say =F2?
 

Jordie

Board Regular
Joined
Feb 3, 2005
Messages
58
display column to left

Actually, I want to display two columns to the left (so F2 is not the cell to display).

Can I simply index to the left?

jordie
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Might you have 2 values tied for largest? If not you could use this formula

=INDEX($B$2:$B$325,MATCH(TRUE,$D$2:$D$325=LARGE(($C$2:$C$325=F2)*($D$2:$D$325),1),0))

confirmed with CTRL+SHIFT+ENTER

If there is a tie this will give the first
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
9,999
book1
ABCDEFG
1
2A1a1match in Cb
3A2a2rank3
4A3a3helper formula 1
5A4a4helper formula 2
6A5a5helper formula 3
7B1b1ANSWER (using helpers)
8B2b2ANSWER (using "mega formula")
9B3b33
10B4b44
11B5b55
12C1c1
13C2c2
14C3c3
15C4c4
Sheet1


<ul>[*]G4 is {=LARGE($D$2:$D$15*($C$2:$C$15=$F$2),F3)}[*]G5 is =MATCH($F$2,$C$2:$C$15,0)[*]G6 is =MATCH(G4,OFFSET($D$2:$D$15,MATCH($F$2,$C$2:$C$15,0)-1,0),0)[*]G7 is =INDEX(B2:B15,G5+G6-1)[*]G8 (mega formula) is {=INDEX($B$2:$B$15,MATCH($F$2,$C$2:$C$15,0)+MATCH(LARGE($D$2:$D$15*($C$2:$C$15=$F$2),F3),OFFSET($D$2:$D$15,MATCH($F$2,$C$2:$C$15,0)-1,0),0)-1)}[/list]

***
I was just getting ready to post when I got the message on Barry's post. Barry, I was able to get your formula to fail if I put repeated values in D even if no match against col C. (Don't know if I'm explaining this very well.)
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
9,999
Oh, fiddlesticks! I just realized -- my sample data is sorted on column C. I don't think this will work well if that's not your situation. :confused: I'll have to think some more on this, meanwhile maybe someone else will show up with a flash of genius...
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Greg,

I'm not sure what you mean about repeated values. I think my suggestion was flawed in that it could give the wrong result if the largest value was zero
but I believe this would fix that:

=INDEX($B$2:$B$325,MATCH(1,($C$2:$C$325=F2)*($D$2:$D$325=LARGE(($C$2:$C$325=F2)*($D$2:$D$325),1)),0))

confirmed with CTRL+SHIFT+ENTER
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
9,999
Barry, your second formula works very well for me. And the data does not have to be sorted! (Mine imploded when I shuffled the data. :cry:) Nice job. (y) Let's hope our OP has similar good results with it.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,119
Greg Truby said:
...Let's hope our OP has similar good results with it.
Combining LARGE with lookup formulas is just risky (without some sort of uniquifying the result set of interest).
 

Forum statistics

Threads
1,077,783
Messages
5,336,304
Members
399,074
Latest member
rlong98

Some videos you may like

This Week's Hot Topics

Top