# largest three numbers - display cell to left of it

#### Jordie

##### Board Regular
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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

##### MrExcel MVP
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
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
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
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
book1
ABCDEFG
1
2A1a1match in Cb
3A2a2rank3
4A3a3helper formula 133
5A4a4helper formula 26
6A5a5helper formula 33
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
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. 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
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
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. ) Nice job. Let's hope our OP has similar good results with it.

##### MrExcel MVP
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).

Replies
10
Views
237
Replies
17
Views
304
Replies
2
Views
133
Replies
2
Views
106
Replies
0
Views
340

1,195,849
Messages
6,011,953
Members
441,657
Latest member
Diupsy

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

### Which adblocker are you using?

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

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