[Easy Excel] VLOOKUP and INDEX MATCH

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

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

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

Advertisements
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

    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

    Like

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

      Like

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

    Like

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s