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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
book1
ABCDEFG
1
2A1a1match in Cb
3A2a2rank3
4A3a3helper formula 133
5A4a4helper formula 26
6A5a5helper formula 33
7B1b1ANSWER (using helpers)B3
8B2b2ANSWER (using "mega formula")B3
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.)
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,213,581
Messages
6,114,435
Members
448,573
Latest member
BEDE

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top