I was finishing my task for water resource management course when suddenly i remember that i need to write about “VLOOKUP” and “INDEX MATCH”. Why? simply because i often forget about INDEX MATCH formula. While the VLOOKUP is the additional because i think it’s easier to understand INDEX MATCH after taste what VLOOKUP is.

**VLOOKUP**

VLOOKUP stands for Vertical Look Up. To make it easier, let’s say we have a list of information in a table. Let’s say… inflow table… see this..

Vlookup @ inflow table

Let’s say we have the data in that yellow table and then we have the orange table. We want to know, how much the inflow in October, May, and January are. So we can type that formula…. btw…. Oooopsss… i’ve written about this in my previous post. Check this out.. ^^V

INDEX MATCH

Now, let’s move to the “INDEX MATCH”. Principally, it works like VLOOKUP. When VLOOKUP read the data to the right column, INDEX MATCH works vice versa. It looks in the right column, and work to the left.

Let’s see another table…

index match

So, the previous table (from column “Y” to column “AD”) as the data. Cell “AE8” bellow the *f4(S4)* column is the maximum value of the previous column. We’d like to know how much is the “*X4*” (column Z) when column AD is equal to its maximum value (see the **green highlight**). *I use a conditional formatting for that green highlight, by the way…

Ah, let’s get this even much easier by trying those formulas on this excel file i provide for you :ย Example

Enjoy..!!

-7.797224
110.368797

### Like this:

Like Loading...

# =INDEX(Z8:AD16,MATCH(AE8,AD8:AD16,0),1)

I have try it, put this index formula on cell AF (X4(S4)) and the result is like below : are these results true?

Column AF

X4(S4)

row 8 => 2

9 => 0

10 => 3

11 => 3

12 => 4

13 => 5

14 => 6

15 => 7

16 => 8

let discuss it !!

regards

LikeLike

So the INDEX-MATCH formula is used to call the value in column Z (X4), based on the value in cell “AE8” (f4(S4)), which is (in this case) the maximum value of range “AD8:AD16”.

It works like Vlookup, but it read the data to the left side of the lookup cell (cell “AE8”), kind of.. “If cell AE8 is 9.6, which one is the right value of X4 (column Z).

We might want to try it for cell AF8, AF17, AF26, AF35, ect…

LikeLike

tq very much… a nice post… please explain to, what is the excel formula above solving for?

LikeLike