The VLOOKUP allows you to take a value in one list, locate that value in another list, then return an associated piece of information.
It would be like ordering lunch where you locate an item in the menu, then return the price to see if you can afford the item.
In our example below, we have a list of “Account #” (column A) and need the associated “Description” in column B.
A separate table has the “Account #” (column G) and “Descriptions” in column H.
We can write a VLOOKUP formula that says, “Take the “Account #” in cell A3 and locate it in “Master Data” table (columns G:H). When found, move over to the 2nd column in the “Master Data” table and return the item on the same row.”
=VLOOKUP(A3, G3:H14, 2, False)
The “False” argument at the end tells VLOOKUP to locate the “Account #” exactly, otherwise, return an error message. If you are looking for items that are “close” to the object being searched for, you can write “True” here or leave it empty. This equates to what is known as an “Approximate Match” compared to an “Exact match”.
NOTE: When constructing the table to be searched (G3:H14) ensure that the leftmost (i.e., “first” column) contains the data to be searched. This is a requirement of the VLOOKUP function. The column of associated data being returned does not need to be directly next to the “first” column, but it does need to be to the right of the “first” column.
To see more examples of VLOOKUP (and HLOOKUP) in action, click the following link for some great practical examples of the functions.
XelPlus – VLOOKUP & HLOOKUP for Dynamic Lookups
AMAZING NEWS!!!!
If you are running a Microsoft 365 (formerly known as Office 365) subscription version of Excel, you have access to a new lookup function called XLOOKUP.
XLOOKUP is much more flexible, feature-rich, and easier to use. Check out these videos to learn more about this amazing new function.
ليست هناك تعليقات:
إرسال تعليق