Retrieve a number value from a cell corresponding to the same row but different column of another ce-CodePudding

I use LARGE to find the (cell with the) largest number in column G. How do I retrieve the number from the same ROW as in G but in column F (so I can add them together)?

CodePudding user response：

Do you want to retrieve the corresponding value or retrieve the sum of the two values?

In order to retrieve just the corresponding value you need to use a VLOOKUP. But, because you cannot return a negative column value such as column F by searching Column G, you have to use an in-formula array to rearrange the columns within the calculation. Here is the formula:

=ArrayFormula(VLOOKUP(large(G2:G,1),{G2:G,F2:F},2, FALSE))

This is assuming that row 1 is a header row.

CodePudding user response：

try:

=SUM(INDEX(IFNA(VLOOKUP(LARGE(G2:G, 1), G2:F, {1,2}, ))))

or:

=SUM(INDEX(IFNA(VLOOKUP(MAX(G2:G, G2:F, {1,2}, ))))