How to return a value if the VLOOKUP value is in a range
Till now we have learnt about VLOOKUP and how to make VLOOKUP range dynamic with the help of excel MATCH function. Now let us learn how to return a value if the VLOOKUP value is in a range. Recently I got a data set where in i was asked to lookup values but the lookup reference table has data in range only.
Let us see the below image to understand this scenario then we will discuss how to selve this issue.
In the above image we can see there are two tables. The one which is on left side Marks column and Category Assignment. The second table has three columns, Category Min,Category Max and Category Name. Now we wanted to lookup value in Category Assignment column against the values in Marks column. In normal scenario definitely we would have done this by using VLOOKUP excel function.
But in this scenario our values in the lookup table is not in the continuous form, so it makes it tricky to get value the Category assignment column. But don’t worry we have a solution here which is already available in the VLOOKUP formual. Now look into the below image.
In this example we use vlookup function but we used range_lookup of the excel vlookup function as TRUE instead of FALSE.
How TRUE option in range_lookup works?
While on one side FALSE option picks only Exact matches on the other side TRUE picks only Aproximate Match. In the above scenario lets suppose we try to look for Category Assignment value for Marks 52. Now when we see in the second table(table_array) there is no exact match available for 52, we see the there is a value of 50 available which is approximate match for 52. So now it will pick value Category-E from column Category Name.
In the same way it will pick values against all lookup values.There is one more important thing to be noted here is that from value 60 to 69 it will pick only value of Category-D. This is true for all values available in Category Min column.
Last point that wanted to highlight that Category Min column should be sort in ascending to descending otherwise this will not work. This is due to the reason that lookup searches value in the column one by one.
That is all for this tricky How to return a value if the VLOOKUP value is in a range. Will meet again with some new topic.
You can read more on this topic from the below articles.