Index Match instead of Vlookup

[Jonathan9]

Hi,

I'm wrestling with quite heavy excel sheets and they demand a lot of resource so Excel often crashes. So I've been told that I should use index match instead of vlookup so I turned to my friend Google - but he failed in explaining how to structure the syntax to give the same result as I would use vlookup.

So if I were to use Index Match instead of vlookup - how would I write the syntax?

Thanks.

[benishiryo]

hi Jonathan. INDEX/MATCH is indeed faster, but i'm not sure if it will make a whole lot of difference. Charles Kyd did a test on it before, but the bigger difference was when everything was sorted and/or separating the formulas into parts
http://exceluser.com/formulas/why-in...an-vlookup.htm

if you know your VLOOKUP, it returns a value. the MATCH formula does something similar, but returns the row/column number of the range instead. say "Apple" is found in A3. the price is in column C so if your VLOOKUP looks like this:
=VLOOKUP("Apple",A2:C5,3,0)

the MATCH formula can only look up a single column or row. so it's always ranged where you can find "Apple".
=MATCH("Apple",A2:A5,0)
since it's only a single column (column A), you don't have to tell the formula which col_index_num to take from like the VLOOKUP formula. and instead of returning the price, it returns you the number 2. that's the row position from the range A2:A5

with INDEX, you just need to tell them which range you want to take from. since it's in C2:C5,
=INDEX(C2:C5,MATCH("Apple",A2:A5,0))

[Moo the Dog]

If you had this: =VLOOKUP(A1,B1:C25,2,0)

Then the INDEX/MATCH version would be: =INDEX(C1:C25,MATCH(A1,B1:B25,0))

- Moo

[Jonmo1]

Basically

=INDEX(columntoreturnvaluefrom,MATCH(valuetofind,columntofindvaluein,0))

The 0 could be either 0 or 1
Use 0 if your original vlookup used FALSE as the 4th argument.
Use 1 if it used TRUE or it was omitted from the vlookup.

This by itself isn't a HUGE difference.

Where it makes a real difference is when you are returning Mulitple column values based on the same lookup value.
With vlookup you would do
=VLOOKUP(val,range,2,False)
=VLOOKUP(val,range,3,False)
=VLOOKUP(val,range,4,False)
etc..
This is very repetetive, it's doing the same lookup multiple times, but returning values from a different column.

In this case, performance can be greatly improved with Index/Match.
Put the match in a cell by itself, say Z1 or whatever...
=MATCH(valuetofind,columntofindin,0)
Then do
=INDEX(columntoreturnvaluefrom,$Z1)

As you drag the index formula to the right, the columntoreturn incriments automatically.
But each formula refers to Z1, so the match is only being done once.


Hope that helps.