Need to understand how Index Large and Column functions working in this formula

[Pawnar]

Hello,

INDEX($B$1:$L$1,0,LARGE(IF(($A$2:$A$8=D15)*($B$2:$L$8="L"),COLUMN($B$1:$L$1),0),1)-1)

I have this formula which takes me to column number 6. I need to understand how it is reaching to column no 6.

I'm attaching a excel sheet as a reference.

Thank you,

Manish

[Pawnar]

I used -1 to reach column no 5 to get my answer. but I need to understand how it is reaching to column no 6.

Thank you

[menem]

From
INDEX($B$1:$L$1,0,LARGE(IF(($A$2:$A$8=D15)*($B$2:$L$8="L"),COLUMN($B$1:$L$1),0),1)-1)


INDEX($B$1:$L$1,0,LARGE(IF(($A$2:$A$8=D15)*($B$2:$L$8="L"),COLUMN($B$1:$L$1),0),1)-1)
This part is refer to column number of B to L (for use in LARGE function)
{2,3,4,5,6,7,8,9,10,11,12}


INDEX($B$1:$L$1,0,LARGE(IF(($A$2:$A$8=D15)*($B$2:$L$8="L"),COLUMN($B$1:$L$1),0),1)-1)
{
0,0,0,0,0,0,0,0,0,0,0; for Mukesh
0,0,0,0,0,0,0,0,0,0,0; for Deepak
0,0,0,0,0,0,0,0,0,0,0; for Raju
0,0,0,0,0,0,0,0,0,0,0; for Pankaj
0,0,0,0,0,0,0,0,0,0,0; for Suresh
0,0,0,0,0,0,0,0,0,0,0; for Hitesh
0,3,0,0,6,0,0,0,0,0,0 for Amit
}

so, maximum number is 6 because come from 5th column in the range (but it is 6th column of worksheet).



Regards.

[WideBoyDixon]

You could use it like this instead:

Please Login or Register  to view this content.

Also enter with Ctrl+Shift+Enter to create an array formula. There's also an AGGREGATE solution if you have that function:

Please Login or Register  to view this content.

Note that this doesn't need to be an array formula.

WBD

[Pawnar]

@-menem - Perfect! Thank you so much

@-WideBoyDixon - Awesome, thank you so much

I have just a follow up question.

I'm trying to get starting value and ending value from the data for a learning purpose.

Here is the formula I'm trying to apply to get for starting values: - INDEX($B$2:$B$26,SMALL(IF($A$2:$A$26=$G2,ROW($B$2:$B$26),""),ROW(A1))-1)
Here is the formula I'm trying to apply to get for ending values : - INDEX($B$2:$B$26,LARGE(IF($A$2:$A$26=$G2,ROW($B$2:$B$26),""),ROW(A1))-1)

I'm missing a logic here again. Could you please help.

Thank you.

Manish

[Pawnar]

Further explained

There is a column A and Column B.

Column A contain a list of 3 fruits Apple Mango and Orange from A2 to A30
Column B contain numeric values

I need to obtain starting value and ending value of apple mango and orange respectively

Thank you

Manish

[WideBoyDixon]

That seems to be a different question. Can you post a new workbook with example of what you're trying to achieve?

WBD

[Pawnar]

Hello WBD,

I really wanted to add a file but I did not get that option. I have copied and pasted the data and its format below:

Attachment 645641

Please assist

Thank you.

Manish

[Pawnar]

You can add more random numbers in price column to get the outcome. Thank you, Manish.

[WideBoyDixon]

Hello WBD,

I really wanted to add a file but I did not get that option. I have copied and pasted the data and its format below:

Attachment 645641

Please assist

Thank you.

Manish

That attachment didn't work.

[Pawnar]

Oh!!

I will try to explain this way:

Column A: Column B:
Name of Fruits Price

For Column A - There is a random list of 3 fruits Apple, Mango and Orange (A2:A21)
For Column B - Column B has a random numeric numbers (A2:A21)

I need to obtain a Starting Price and a End Price of each fruits in below format:

Fruits Starting Price End Price
Apple
Mango
Orange

I have used below formula to get my output. But that's not correct.

starting values: - INDEX($B$2:$B$26,SMALL(IF($A$2:$A$26=$G2,ROW($B$2:$B$26),""),ROW(A1))-1)
ending values : - INDEX($B$2:$B$26,LARGE(IF($A$2:$A$26=$G2,ROW($B$2:$B$26),""),ROW(A1))-1)

Please suggest.

Thank you.

Manish

PS: - cud you also share the steps with me to attach a file. TYSVM

[Pete_UK]

You attached a file to your first post.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Don't try to use the Paperclip icon, as it doesn't work on this forum.

Hope this helps.

Pete

[Pawnar]

Gotcha!!

Thank you so very much. I was trying to get that option via Quick Reply.

That worked for me.

[Pawnar]

Hello,

The highlighted part in the attached is the area where I'm trying to get starting value and ending value of each fruit listed in Col A

Please suggest.

Thank you.

Regards,
Manish Panwar

[Pawnar]

Starting Price Value and Ending Price Value***

[WideBoyDixon]

$H2 and $I2 formulas:

Please Login or Register  to view this content.

Enter both with Ctrl+Shift+Enter to create an array formula then copy down to H3:I4.

Alternatively, if you want to use AGGREGATE:

Please Login or Register  to view this content.

These don't need to be entered with Ctrl+Shift+Enter.

WBD

[FlameRetired]

Another way.

In H2 filled down and across both columns.

Please Login or Register  to view this content.

[Pawnar]

Thank you so very much!!

I had just one last follow up question in the same regard. I have attached the file and looking for the answer in the same manner as highlighted in the sheet.

Looking for min starting value and max ending value for each fruits.

Appreciate the help!!

[Pete_UK]

Use these array* formulae in the cells stated:

I3: =MIN(IF($A$2:$A$26=$H3,$B$2:$B$26))

J3: =MAX(IF($A$2:$A$26=$H3,$B$2:$B$26))

*Note that an array formula needs to be committed using the key combination of Ctrl-Shift-Enter, instead of the usual Enter.

Then copy them down as required.

Hope this helps.

Pete

[Pawnar]

Yes!! Certainly, Pete.

Thank you so very much everyone for your support. My queries are resolved.

Regards,
Manish

[Pawnar]

Thank you WBD

Thank you menem

[Pete_UK]

Glad to hear that your questions have been solved.

Perhaps nobody has told you, so you might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of any post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete