# Return Highest & Lowest Value from Vlookup

1. ## Return Highest & Lowest Value from Vlookup

So I am needing to return the highest or lowest value from a vlookup when the lookup_value is outside of the table

Here is my basic vlookup forumla: =VLOOKUP(E11,\$J\$10:\$K\$40,2,FALSE)

Here is my J10 to K40
40 200
39 194
38 188
37 182
36 176
35 170
34 164
33 158
32 152
31 146
30 140
29 134
28 128
27 122
26 116
25 110
24 104
23 98
22 92
21 86
20 80
19 74
18 68
17 62
16 56
15 50
14 44
13 38
12 32
11 26
10 15
9 0

So what I am trying to do is if a lookup_value is 9 or less / 40 or more is to return the lowest (being 0) or highest (being 200)

Just not sure if it is possible and if it is, how to do it!

2. ## Re: Return Highest & Lowest Value from Vlookup

maybe something like this:
=IF({source}>=MAX(\$J\$10:\$J\$40),MAX(\$K\$10:\$K\$40),IF({source}<=MIN(\$J\$10:\$J\$40),MIN(\$K\$10:\$K\$40),VLOOKUP({source},\$J\$10:\$K\$40,2,FALSE)))

There are currently 1 users browsing this thread. (0 members and 1 guests)