PDA

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



white_flag
02-18-2011, 12:19 PM
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
02-18-2011, 12:27 PM
Don't understand. Give some examples.

white_flag
02-19-2011, 03:54 AM
morning,

please look in attachment

Bob Phillips
02-19-2011, 05:05 AM
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
02-19-2011, 05:55 AM
thank you, it is working fantastic..
an small correction

=IF(ISNA(MATCH(A20,col1,0)),MIN(IF(col1>A20,col1)),VLOOKUP(A20,col1,1))


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

have an nice weekend