Explain Index Match formula

**ddub25** · 06-21-2012, 03:53 AM

Thanks for a very useful breakdown - makes things a lot clearer.

Dan

**soberguy** · 06-21-2012, 03:56 AM

I end up building them that way as well. I have to do it step by step still, starting with the match. If matching two criteria, I get one working, then the second, then wrap them together, then stick that in the Index, and if that goes well, then I wrap it in If(IsError(

It can be pretty confusing stuff, but I'm become extremely comfortable with it after only a week. Persistence is the key

EDIT: In re-reading, daddylonglegs is spot on. My mistake! I'm used to wrapping these in the If(IsError to return "" if there is no value to return. He's absolutely correct about the intersection value for the column, and the 1 is simply returned for when the values do not line up. Apologies!

**ddub25** · 06-21-2012, 04:14 AM

Yes, the only way I can build a more complicated formula is by creating each step and then latching them together. I'm not sure of the best process to build formulas as probably not spent enough time going through excel tutorials. I use Notepad++ and put each arguement on each row. I know that it is not very efficient because I often end up covering the same condition in another arguement on other rows. Example:

=IF(AND(L216="W",C216="IB"),(I216*G216),
IF(AND(L216="W",C216="CB"),(I216*G216),
IF(AND(L216="W",C216="FB-SR"),(I216*G216),
IF(AND(L216="W",C216="FB-SR"),(I216*G216-I216),
IF(AND(L216="V",C216="IB"),I216,
IF(AND(L216="V",C216="CB"),I216,
0))))))

I am sure this could written more efficiently, but its the only way I know how to write it.

You have become comfortable with Excel after only a week? Or MATCH and INDEX after only a week?

**zbor** · 06-21-2012, 04:19 AM

Assuming that second is IF(AND(L216="V",C216="FB-SR"),(I216*G216-I216),

then you can shorten it as:

=I216*IF(L216="W", G216, IF(C216="FB-SR", G216-1, 1))

Basically, creating table and using INDEX/MATCH approach would be easier than lot of IF's.

**ddub25** · 06-21-2012, 04:28 AM

Thanks zbor much appreciated. Another formula on the same sheet I want to adapt is:

=IF(OR(Q118={"$","€"}),VLOOKUP(E118,[RefHidden_120112.xlsm]Ref!$BY$5:$CB$60,MATCH(Q118,[RefHidden_120112.xlsm]Ref!$BY$3:$CB$3,0)),"1.00")

I want it to return "--" IF Q118="". I am not sure of the syntax to add the extra IF.