2.1. LOOKUP & REFERENCE - VLOOKUP PART 2

Hello Excel-lent,

Happy 55th Independence!

We started off with VLOOKUP during our last session but today, we will discuss about the VLOOKUP's COUSINS and they are HLOOUP, MATCH, INDEX and CHOOSE. I so much like these guys because they work as a team and always compliment each other.  VLOOKUP is restricted to leftmost columns but MATCH, INDEX and CHOOSE help VLOOKUP to search for data both ways....

VLookup

The VLOOKUP (Vertical lookup) function looks for a value in the leftmost column of a table, and then returns a value in the same row from another column you specify.

1. Insert the VLOOKUP function shown below.

Explanation: the VLOOKUP function looks for the ID (104) in the leftmost column of the range $E$4:$G$7 and returns the value in the same row from the third column (third argument is set to 3). The fourth argument is set to FALSE to return an exact match or a #N/A error if not found.

2. Drag the VLOOKUP function in cell B2 down to cell B11.

Note: when we drag the VLOOKUP function down, the absolute reference ($E$4:$G$7) stays the same, while the relative reference (A2) changes to A3, A4, A5, etc.

HLookup

In a similar way, you can use the HLOOKUP (Horizontal lookup) function.

Match

The MATCH function returns the position of a value in a given range.

Note: Yellow found at position 3 in the range E4:E7. The third argument is optional. Set this argument to 0 to return the position of the value that is exactly equal to lookup_value (A2) or a #N/A error if not found.

Index

The INDEX function returns a specific value in a two-dimensional or one-dimensional range.

Note: 92 found at the intersection of row 3 and column 2 in the range E4:F7.

Note: 97 found at position 3 in the range E4:E7.

Choose

The CHOOSE function returns a value from a list of values, based on a position number.

Most people are familiar with One-column VLOOKUP but in our next class, we will discuss on how you can do a two-column VLOOKUP. Don't miss it!

Wishing you a happy 55th Independence celebration.

Excel-lently yours,

Oladapo Sorinola
07014282477, 07062932708
BB Pin 52E9802D