![]() VLOOKUP has two modes of operation: exact match and approximate match. We also offer paid training for VLOOKUP and INDEX/MATCH 5. In the example below, we are looking for uppercase "JANET" but VLOOKUP does not distinguish case so it simply matches "Janet", since that's the first match it finds: To VLOOKUP, a product code like "PQRF" is identical to "pqrf". When looking up a value, VLOOKUP does not process upper and lower case text differently. This article explains the topic in detail. Note: behavior can change when VLOOKUP is used in approximate match mode. Although there are two "Janet"s in the list, VLOOKUP matches only the first: In the example below, we are using VLOOKUP to find a first name, and VLOOKUP is set to perform exact match. In exact match mode, if a lookup column contains duplicate values, VLOOKUP will match the first value only. You can overcome this limitation by using INDEX and MATCH instead of VLOOKUP. You'll also have to supply a smaller table to VLOOKUP that starts with the lookup column. However, if the lookup column appears inside the table somewhere, you'll only be able to lookup values from columns to the right of that column. When lookup values appear in the first (leftmost) column, this limitation doesn't mean much, since all other columns are already to the right. This means that VLOOKUP can only get data from columns to the right of first column in the table. Perhaps the biggest limitation of VLOOKUP is that it can only look to the right to retrieve data. If you still don't get the basic idea of VLOOKUP, Jon Acampora, over at Excel Campus, has a great explanation based on the Starbucks coffee menu.
0 Comments
Leave a Reply. |