Here I am going to show you how we can create a custom Reverse lookup function in MS excel.
For this we need to go activate the Developer tab in the Menu bar.
To activate the developer tab, please follow the below steps:
In the vba ide window, copy paste the below code:
Function revlookup(returnrange As Range, lookup_value As Variant, lookup_range As Range)
revlookup = WorksheetFunction.Index(returnrange, WorksheetFunction.Match(lookup_value, lookup_range, 0))
End Function
The function is now ready to use. Lets try it by going back to the Excel Sheet.
Now, this formula has 3 components:
- Return Range - The range from where we need the results
- lookup Value
- Lookup Range - The range where our lookup value is located
In the above scenario
- Return Range is A3:A10
- Lookup value is Texas
- Lookup Range is B3:B10
👇
Thank You!!