lookup a cell address with criteria value

[abrunstad]

When wanting to lookup the cell address of a value (ex 999)..

I know from previous reading that the cell address of exact value is:
=ADDRESS(MATCH((999),$A$1:A7,0),1)

However, when editing to:
ADDRESS(MATCH((999),$A$1:A7,-1),1)

to get the cell address with value that is bigger than 999, i get an error

Why?

attached sheet shows the example

[NBVC]

If you want the address of the first cell that is bigger than your indicated number then try:

=ADDRESS(MATCH(TRUE,A1:A8>999,0),1)

confirmed with CTRL+SHIFT+ENTER

or

=ADDRESS(MATCH(TRUE,INDEX(A1:A8>999,0),0),1)

Confirmed normally with only ENTER

[ChemistB]

I can't look at your file as I do not have Excel 2007 but the 0 in the MATCH function states that values can be in any order. The -1 will only work if the values are sorted in decending order.

[modytrane]

read the description of MATCH function syntax below.
what you are looking or, requires that the numbers are in descending order.
See the attached example.
modytrane

[modytrane]

sorry, forgot to paste the syntax description.

Match_type is the number -1, 0, or 1. Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.

If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.

If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.

If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

If match_type is omitted, it is assumed to be 1.

[abrunstad]

Thanks for replys guys!

there is still one problem,

=ADDRESS(MATCH((1000),Produksjonplan!B13:CB13,-1),1)

I am looking up in a single row, and many colums in another sheet (b13:cb13) in the sheet "produksjonsplan".
is it he last 1-digit that now is my problem? :S

[NBVC]

Should it be?

=ADDRESS(13,MATCH((1000),Produksjonplan!B13:CB13,-1))

[modytrane]

change your formula to:

=ADDRESS(13,MATCH((1000),Produksjonplan!B13:CB13,-1))

make sure the numbers are in descending order.

modytrane