Lookup formula based on a returned value

[voltrader]

Hey all,

I retrieved a value from a range, and I am trying to apply a LOOKUP to retrieve a corresponding value, in the cell next to the returned value cell.
In the range that I am pulling the data from the corresponding value is to the left of the first value, so a VLOOKUP will not work.

The first formula I am using =MAX('Page'!$I$2:$I$45) returns the MAX, and I want to use a LOOKUP type formula to return the name in a range that corresponds to the MAX. The named cells are 6 columns to the left of the column of the first value. I tried tinkering with MATCH, and INDEX but could not find my way.

All help is appreciated.

 

[Jonmo1]

So based on =MAX('Page'!$I$2:$I$45)
You want the corresponding value from column C (6 columns to the left) ?

Try

=INDEX('Page'!$C$2:$C$45,MATCH(MAX('Page'!$I$2:$I$45),'Page'!$I$2:$I$45,0))

Change the C's to whichver column you want to return a value from..

 

[voltrader]

So based on =MAX('Page'!$I$2:$I$45)
You want the corresponding value from column C (6 columns to the left) ?

Try

=INDEX('Page'!$C$2:$C$45,MATCH(MAX('Page'!$I$2:$I$45),'Page'!$I$2:$I$45,0))

Change the C's to whichver column you want to return a value from..

(you) that's awesome, it works!

I am having some trouble understanding the INDEX, MATCH functionality along with the syntax. My idea of the functions are INDEX defines a range to be searched, while MATCH defines the position of the column, and row for the base result?

 

[Jonmo1]

That's pretty much right, but I wouldn't call it a search really..
Index just goes directly to the specified row/column within the indexed range.
The Match does the search, and returns the row#/col#

 

[voltrader]

Gotcha, I only don't understand the syntax/ mechanism of the formula. Would it make more sense to first search for MAX, and then search the INDEX for the value that corresponds to the MAX result?

Rather the formula that works searches an INDEX first, and then MATCH that INDEX to the desired value? It sounds liek an extra step to have the INDEX rather than finding the MATCH first?

 

[Jonmo1]

That's what the () are for...
It calculates anything in () first..

So the first thing calculated is the MAX
Then the MATCH(resultofmax,...)
Then the INDEX(range,resultofmatch)

You can use the formula auditor to watch it happen..
Tools - Formula Auditing - Evaluate Formula

 

[voltrader]

Thanks, that's a great explanation. I've always been looking at excel formulas differently.

Best !