Solved: Vlookup if not match, find the first bigest value

[white_flag]

Hello

I have this array
col1
125
145
136
189
569
578
145
256
etc

and I have the second array

col1......... col2
132..........the first bigest value from array1 IF do not match
156
185
254
269
369
287
569
etc

it is possible to tell to vlookup to find the value from array1 and if did not mach the value to take the FIRST bigest value from the array1.

[Bob Phillips]

Don't understand. Give some examples.

[white_flag]

morning,

please look in attachment

[Bob Phillips]

Try this array formula

=IF(ISNA(MATCH(A15,$A$2:$A$9,0)),MIN(IF(A2:A9>A15,A2:A9)),MATCH(A15,$A$2:$A $9,0))

[white_flag]

thank you, it is working fantastic..
an small correction
[VBA]
=IF(ISNA(MATCH(A20,col1,0)),MIN(IF(col1>A20,col1)),VLOOKUP(A20,col1,1))
[/VBA]

otherwise match will return position (but this is an detail)..

have an nice weekend