Please explain this formula

[raze]

Could some one please take me through this formula step by step. I think its what I need to create a daily calander I want to make. I just need to know what its doing so I can customize it to my sheet.

Please Login or Register  to view this content.

[djapigo]

Look under Help (F1) for more detailed understanding...

INDEX(range,row,column) = find the value in the range, where starting on the upper left of the range, you "move" by the number of rows and by the number of columns

MATCH(item,range,type) = find where the item is within the range (type = 0 just means exact match)

So, the first MATCH finds where B2 is in the range (this will be used as the row for the INDEX function)
The second MATCH finds where F1 is in the range (this will be used as the column for the INDEX function)
Finally, the INDEX function will find the value in Data range, where we now have the row (first MATCH) and the column (2nd MATCH).

Hope that helps...

[Pete_UK]

The formula will extract a single cell's data from the table A1:F10 in the Data sheet of the indexmatch.xls file (which must be open in the same instance of Excel). The first MATCH function will determine which row to get the data from, by looking to see if there is a match between B2 of the sheet where the formula resides and the range B1:B10 of the Data sheet in the same file, and the second MATCH will determine which column to get the data from by looking to see where F1 of the current sheet occurs in the range A1:F1 of the Data sheet.

Hope this helps.

Pete

[raze]

how would the code look if I am looking at a sheet within the same work book? Would I just remove the [indexmatch.xls] portion and replace it with [workbookname.xls]?

[jeffreybrown]

If it is within the same workbook then you don't need the reference to a .xls at all...

Maybe this reference will help...

http://www.contextures.com/xlFunctions03.html