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 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


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

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


This entry was posted in Engineering, Excel, Software and tagged , , . Bookmark the permalink.

3 Responses to [Easy Excel] VLOOKUP and INDEX MATCH

  1. # =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


    row 8 => 2
    9 => 0
    10 => 3
    11 => 3
    12 => 4
    13 => 5
    14 => 6
    15 => 7
    16 => 8

    let discuss it !!



    • ida says:

      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…


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


leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s