Largest value from every other cell and the cell to the right of the large?

[Waboku]

Hello,

I have 2 questions,

How do I get the largest value from a row using only the values from every other cell? Specifically from the cells B2, D2, and F2. I think I can add the other cells I would like to get the large from if I know how that much of it is formatted.

Also, how do i get the value of the cell to the right of the largest value using a function?

I would like to use functions for the above in the 2 cells to the right of the data. Please and thank you for considering my question.

 

[mrshl9898]

In H2: =INDEX(A2:H2,A2,MATCH(MAX(B2,D2,F2),A2:F2,0)+2)

 

[Waboku]

I'm sorry what I'm hoping for is the large of B2, D2, and F2 in one cell and the cell to the right of the large in the cell to the right of the large function cell. Is there a way your function could be modified to do that? Thank you for your help.

 

[mrshl9898]

Sure.

In H2 =MAX(B2,D2,F2)
In I2 =INDEX(A2:H2,A2,MATCH(H2,A2:F2,0)+1)

 

[Waboku]

I'm sorry but the function for I2 returns #Value !

If you don't mind, is there something wrong with the function for I2?

If it looks ok do you or someone else know how to make a function that returns the value 26 cells to the right of the largest cell instead?

Thanks for your help and consideration.

 

[Waboku]

If someone looks at this it is pretty easy for me to modify my data set to have the prices on the left and the quantities on the right. If someone could please help me with a function that equals the cell 26 cells to the right of the largest cell from the data set of prices, thank you.

for example:

Name, Price 1, Price 2, Price 3, Quantity 1, Quantity 2, Quantity 3, =LARGE(B2:D2,1), =3 cells to the right of the largest price.

 

[mrshl9898]

Hi. Does the below match your query?

[table="width: 500"]
[tr]
[td]A[/td]
[td]B[/td]
[td]C[/td]
[td]D[/td]
[td]E[/td]
[td]F[/td]
[td]G[/td]
[td]H[/td]
[td]I[/td]
[/tr]
[tr]
[td] [/td]
[td]4[/td]
[td]egg[/td]
[td]3[/td]
[td]banana[/td]
[td]2[/td]
[td]oats[/td]
[td]=MAX(B2,D2,F2)[/td]
[td]=INDEX(A2:H2,A2,MATCH(H2,A2:F2,0)+1)[/td]
[/tr]
[/table]


if you want it 26 cells to the right you'd just need to change the +1 to +26.

INDEX returns the location of the cell with the value in H2.

 

[Waboku]

Hello,

In my data set A2 is the name of the item then the cells to the right alternate between a price and qty of the item. I couldn't get the index/match function to work. I don't know what could be wrong but thank you for your help.

 

[mrshl9898]

Thanks, looks like format was causing an issue.

Try this in I2 - =INDEX(A:G,ROW(A2),MATCH(H2,A2:G2,0)+1)

 

[Waboku]

Hello,

Thank you for your continued help. I don't know why but that function returned some values from the prices columns instead of the quantities associated with the max when I applied the function to a larger data set.

This is the function I used for the larger data set:
=INDEX(A:AC,ROW(A2),MATCH(AE2,A2:AC2,0)+1)

AC is what G was, AE2 is what H2 was, AC2 is what G2 was.

If you can see whats wrong or if anyone else knows what to do please consider helping me. Thanks.