Excel VBA formulas for Spreadsheet by Sharath Kumar Peechara - HTML preview

Exercise 10: Create Rlookup (Right lookup) Function

1. Create a UDF Rlookupname (User Defined Function)

To create this Function, execute the following steps

1. Open Excel VBA (Altl + F11)
2. Insert a New Module
3. In the Project Explorer, double click on the Module
4. Add the following code line:

Define Name With Below Range

score :A2:C8

screval :C2:C8

Scorval:A2:A8

1. Open Excel VBA (Altl + F11)

Insert the below code in a New Module

This Code lines will create function Rlookupname ()

In Cell D1 Type

= Rlookupname(88)

It will show result As 4

2. Use Excel Formulas (Index,Match)

Syntax

INDEX(array, row_num, [column_num])

MATCH(lookup_value, lookup_array, [match_type])

=INDEX(A2:C8,(MATCH(G2,C2:C8,0)),2)