dynamic rows in lookup_array

[a_brave_new_analyst]

Hi,

I have been looking around but not found an answer yet, if you have a link, pls post it. If not, here is the problem:

I am looking to use "dynamic rows in lookup_array" of a match function.
The MATCH have the following arguments (lookup_value,lookup_array,match_type).

The "lookup_array"-argument can standard be defined as A1:C10, but I want to be able to have a "dynamic" array.

The coloumn(s) used should be static but I want the row(s) to be able to change.
I have no problem to find out what row nr I want to use but I can not relate it to a lookup_array.

Any ideas?

Thanks

 

[pgc01]

I am looking to use "dynamic rows in lookup_array" of a match function.
The MATCH have the following arguments (lookup_value,lookup_array,match_type).
The "lookup_array"-argument can standard be defined as A1:C10, but I want to be able to have a "dynamic" array.

Hi
Welcome to the board

The lookup array of the Match() function can only have either 1 row or 1 column. A1:C10 is not a valid lookup array for Match().

Maybe you ca post a simple working example?

- 3 or 4 rows with data should be enough
- what you want to do
- the logic, the formula you tried and the expected result

 

[a_brave_new_analyst]

Hi again, sorry for not returning sooner. I have solved the problem with an index function. I post a better description of my problem and the solution I found (I am sure there is a more elegant solution out there). In my master sheet I have two columns. In column A I have dates and in column B I want to load values from a separate sheet that comes from a report printed from other software.

Problem is that report does not stick to one format. Values to be copied into column B in master sheet can be printed in different rows in report, date is however static. However is wanted values always printed in last column.

I need a function that 1 match the date from master sheet column A with date column in report and load the last value of each row (i.e. last column with values). Function I use for this purpose looks like this:

=IF(A5="",0,INDEX('report-sheet'!$A$1:$AA$36,MATCH(A5,'report sheet'!$B$1:$B$36,0),MAX(MATCH(9.99999999999999E+307,report sheet!$A$6:$AA$6),MATCH(REPT("z",255),'report sheet'!$A$6:$AA$6),0)))

If no date in A5 master sheet load zero
Index sheet area $A$1:$AA$36 here will I find my values. Area is larger then I need (better safe than sorry)
Match date in cell A5 (master sheet) with report sheet !$B$1:$B$36
Max function load last value of row nr chosen my date match function.

I hope that this can help someone.

Thanks PGC, hope to hear from you agian.