Using VLOOKUP + MATCH (HLOOKUP + MATCH, OFFSET + 2 MATCHes or INDEX + 2 MATCHes) to search a table in both axis.
This post has been sitting for a while in my archive, waiting for me to get some time to polish and post.
Tag: MATCH
Min Max vehicle
Finding which vehicle with minimum (or maximum) amount of savings among peers.
This is an old request, found its formula in my archive to calculate which of the 4 columns is actually has the minimum (or maximum) of savings among other columns.
Trick is as you guessed it was OFFSET.
OFFSET+MATCH
One of the power of combining functions in one cell is using the best of these functions
An example is here, OFFSET + MATCH will allow you to do something similar to VLOOKUP.
Why do we want to do that?
VLOOKUP will search in the left-most column in a table to get a value from a column on the right. But this is not the situation all the time
We needed several times to locate a row by using a column on the right, then get the value from a column left to it.
This is when VLOOKUP can not help us.