Problem with address look up

[Bunsen]

I have a set of laboratory test results I need to process. Using the MAX and MIN commands I can find the required number in a set of data. Using this

=INDIRECT(ADDRESS(MATCH(R2,B1:B205,0),12))

I can look up the matching data for a different column on the selected row. This works fine except I need to do the calculation 10 times. That is for 10 sets of data within the results lists.

For the next set of data I have modified the formaula to

=INDIRECT(ADDRESS(MATCH(R3,B206:B410,0),12)) so ,I believe, the spreadsheet should now look up the matching value for cell R3 within the range B206:B410 and put this in my results table.

My problem is that the formula only works once in the spread sheet. For the second and subsequent sets of data the spreadsheet does not look uop the value in the correct range but inputs a value taken from the first set.

This happens for all 10 blocks of data. The indirect address match command ignores the column B cell range and still inputs a value from B1 to B205.

I can't seem to find why I cannot look up values from sucessive sets of data and why the spreadsheet only referes to the first lookup cells.

Any help grearlt appreciated

[Pete_UK]

MATCH returns the relative position of the found item, so if your range is B206:B410 and the matching item is in cell B220, for example, then the MATCH function will return 15 - you will need to add 205 (i.e. the number of rows before the start of the range) onto this to get the absolute row number.

Hope this helps.

Pete

[Bunsen]

The spreadsheet works well for the first set of data. For cells 1 to 205 the maximum value is found and put in cell R2.
=INDIRECT(ADDRESS(MATCH(R2,B2:B205,0),12))
The value for the indirect address match is looked up correctly and put in the correct cell in a results table.

The problem is for the next set of data, Cells 206 to 410. The formula for these cells is

=INDIRECT(ADDRESS(MATCH(R3,B206:B410,0),12)

This does not look up the matching data for cell R3 between cells B206 and B410 it selects a cell still from the range B2 to B205. This is not what I need and I can't find any reason why the second address match will not work

[Pete_UK]

Attach a sample Excel workbook (the FAQ describes how to) so we can see more clearly what you are trying to do - there are a number of ways of achieving multiple lookups of duplicated values, so we should be able to suggest alternatives to your formula.

Pete