Originally Posted by
Pete_UK
The INDEX function can return the value from a particular cell within a 1-d or 2-d range. The general syntax is:
=INDEX( range , row_number , column_number )
It is often used in conjunction with the MATCH function, which has the ability to find where a search_cell exists within a 1-d range - its general syntax is:
=MATCH( search_cell , range , type )
where type determines if you are looking for an exact match (value of FALSE or 0 ) or an approximate match (value of TRUE or 1 or omitted).
So the first formula is looking at the range F9:R12, though you might have to extend this by changing the R if you add more data to the right.
The row from which we want to retrieve the data is given by the first MATCH function. This tries to find an exact match between cell A10 and the numbers in cells E9 to E12, although strictly speaking you could just use A$10 instead of the MATCH function here, as the numbers are the same as the row_number that we are looking for. The second MATCH function is trying to find an approximate match between TODAY() and the dates stored in the cells from F6 to R6. With an approximate match the function will return the relative column_number in that range where the values (i.e. the dates) are less than or equal to the search_value (i.e. TODAY() ). For this to work correctly, the values have to be sorted (as yours are). Those two values thus determine the cell where we want the data to be returned from.
As the formula is copied across, the A$10 will change to B$10, then C$10, and so on across, but the other ranges will not change because all the cell references are anchored by having the $ symbol in front of them (called absolute addressing), so we are always trying to find data from the row in the main table determined by the headers on row 10. The other formula is very similar, but some of the ranges are changed to suit the lower table.
Hope this helps.
Pete
P.S. If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.
Also, since you are relatively new to the forum (in terms of post-count), you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).
Pete
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks