Explain Index Match formula

Can anyone explain in words what this formula is doing?

=IFERROR(INDEX([RefHidden_120112.xlsm]Ref!$CA$5:$CB$22,
MATCH(1,IF(TODAY()>=[RefHidden_120112.xlsm]Ref!$BY$5:$BY$22,
IF(TODAY()<=[RefHidden_120112.xlsm]Ref!$BZ$5:$BZ$22,1)),0),
MATCH(B92,[RefHidden_120112.xlsm]Ref!$CA$3:$CB$3,0)),1)

Thanks.

Dan

Thanks for the explanation daddylonglegs, that's very helpful. If you have time to give more detail on the specific mechanics of each function, that would be much appreciated. Thanks.

Dan

Use Help for each function.. It's explained there with examples...
Rather than we coppy paste it here

Ok, thanks zbor

INDEX([RefHidden_120112.xlsm]Ref!$CA$5:$CB$22 sets the table of your data. This is the whole table that will contain the item to lookup and the value to return

IF(TODAY()>=[RefHidden_120112.xlsm]Ref!$BY$5:$BY$22,
IF(TODAY()<=[RefHidden_120112.xlsm]Ref!$BZ$5:$BZ$22,1)),0),
MATCH(B92,[RefHidden_120112.xlsm]Ref!$CA$3:$CB$3,0))tells it what row to the value you want is in. It's looking for where today's date is greater than or equal to a date in the specific area of BY5:BY22 AND in BZ5:BZ22, AND then returning the row number where CA3:CB3 matches what's in B92.

1) tells the index function once it has the row number, to return the data in column 1 of that row, in this case column CA.

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

Dan

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?

Why you have different result on same inputs:

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

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.

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.

Can you open new thread for this since this going off topic now?

yes, sorry.