What formula to use "if" function cant sustain the list? Lookup doesn't help

[DavidRoger]

All the data is not sorted and filled one by one in order throughout.


1 3000
2 2950
3 2700
4 3000
.
.
.
59 2350
60 2400


if there is not data in row 60, the formula should show data in row 59 and so on.

The problem is "if" can't fulfill all the condition.
"Lookup" doesn't fit also.
How can the problem to solve?

 

[Aladin Akyurek]

All the data is not sorted and filled one by one in order throughout.


1 3000
2 2950
3 2700
4 3000
.
.
.
59 2350
60 2400


if there is not data in row 60, the formula should show data in row 59 and so on.

The problem is "if" can't fulfill all the condition.
"Lookup" doesn't fit also.
How can the problem to solve?

Try...

=LOOKUP(9.99999999999999E+307,A:A)

 

[DavidRoger]

Try...

=LOOKUP(9.99999999999999E+307,A:A)

Your formula solve the problem.

If you dont mind, please brief what is 9.99999999999999E+307.

Big Thanks.

 

[Aladin Akyurek]

Your formula solve the problem.

If you dont mind, please brief what is 9.99999999999999E+307.

Here are some links on the subject:

http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html (post #3 and #5)

LOOKUP, and kindred functions with match-type set to 1, using this big number (largest number allowed to be typed into a cell in Excel), will land on the last numeric value in the reference it's given (A:A, 4:4, E2:E25, C4:K4), irrespective of whether the reference is sorted in ascending order. This is a description by effect, not an algorithm. I describe the algorithm involved in:

http://www.mrexcel.com/forum/excel-questions/310278-vlookup-multiple-matches-match-returned.html (post #7)

By the way, the big number is often contracted to 9.99E+307 (in analogy to the number π [Pi]).

The equivalent look up value for text, BigStr, due to Mark W., created here at mrexcel, is:

REPT("z",255)

which is often used in a LOOKUP expression in order to find the last text value in a reference as in:

=LOOKUP(REPT("z",255),A:A)

Big Thanks.

You are welcome.

 

[Marcol]

Hmm?
=LOOKUP(REPT("z",255),A:A) fails if the last string is longer than 255 characters and 255 can't be increased.

ISTEXT() recognises long strings, but not when used in =MAX(INDEX(ISTEXT(A1:A65535)*ROW(A1:A65535),,1))

65535 is the number of rows in 2003 minus 1

This table has been modified to suit the forum, when you apply the formula you will see the results.

Once done enter any text in say A15

It seems to me that LOOKUP(REPT("z",255),A:A) is potentially more fragile than using 99^99 as "BigNo"

Excel Workbook
	A	B	C	D	E	F
1	Data	String Length			aaaaaaaaaaaaaaaa	???
2	xxxx	4
3	xxxxxxxxx	9		13	aaaaaaaaaaaaaaaa
4	xxxxxxxxxxxxxxxx	16
5	xxxxxxxxxxxxxxxx.	25		13	aaaaaaaaaaaaaaaa
6	xxxxxxxxxxxxxxxx.	36
7	xxxxxxxxxxxxxxxx.	49
8	xxxxxxxxxxxxxxxx.	64
9	xxxxxxxxxxxxxxxx.	81
10	xxxxxxxxxxxxxxxx.	100
11	xxxxxxxxxxxxxxxx.	121
12		0
13	aaaaaaaaaaaaaaaa	256
14
15
16
Sheet1