If you have already used VLOOKUP in Excel good number of times, you most probably must have come across a situation where you had to lookup based on multiple columns instead of looking up from a single column. For example, in my article Introduction to VLOOKUP in MS Excel, you can see how VLOOKUP is used to lookup a value from a single column and then return the value from another column corresponding to the row where you found the lookup value. In the example in this article, you just had to return the phone number of a customer based on a given name of the customer.
But now you are given the Office, Residence and Mobile phone numbers of a list of customers as an excel table (table name: Contacts) as shown below:
| Customer Name | Phone Type | Phone Number |
|---|---|---|
| Abraham | Mobile | 7039722049 |
| Srivani | Mobile | 7058769409 |
| Srivani | Office | 7263639502 |
| Stacey | Mobile | 7543532008 |
| Bala | Office | 8084594433 |
| Abraham | Residence | 8290181512 |
| Srivani | Residence | 8302235551 |
| Abraham | Office | 8392510776 |
| Bala | Residence | 8479661404 |
| Stacey | Residence | 8629686227 |
| Bala | Mobile | 8802194763 |
| Stacey | Office | 9914438692 |
Challenge: Your task is to return the office number, residence number and mobile number separately for a given customer. See below:
The formula we entered in the above screenshot is:
=VLOOKUP($E9&F$8, CHOOSE({1,2}, Contacts[Custome Name] & Contacts[Phone Type], Contacts[Phone Number]),2,0)
when you enter the above formula in cell for Customer’s Residence phone number and drag the same formula to cells for Office and Mobile Phone numbers, it magically returns the correct Residence, Office and Mobile phone numbers for the customer.
Explanation:
Step:1. The CHOOSE function in this formula picks up the “Customer Name” and “Phone Type” columns from Contacts table, concatenates corresponding values from these two columns for each of the rows to build a single column. It then takes this new concatenated column and “Phone Number” column and builds the needed lookup range/table for VLOOKUP. Note that the first parameter of CHOOSE function determines the sequence in which the two columns of this lookup range are arranged. In this case, we have the newly formed concatenated column as the first column and “Phone Number” as the second column in this dynamically built range.
Step:2. Now the VLOOKUP function starts by concatenating the customer name (present in cell E9), with “Phone Type” that is present in cells F8, G8 and H8. For example, VLOOKUP function in the formula in cell F9 from the above screenshot, concatenates the customer name present in cell E9 with Phone Type present in cell F8 (i.e., Residence), then it looks up this concatenated value from the first column of the lookup range that is dynamically built in step:1
Note that while concatenating the lookup value in the first parameter of VLOOKUP, we partially fixed the cell references, so that the formula once completely built can be just dragged to determine the phone numbers of other Phone Types as well.
Step:3. Finally, the 3rd parameter in VLOOKUP enumerates the columns in the lookup range (starting from 1) and specifies the column number from which the value is to be returned, when a match is found. In this case, the phone numbers are present in second column of the lookup range that was dynamically built using CHOOSE function in step:1. The last parameter of VLOOKUP helps in specifying the type of matching (0: Exact, non-zero: Approximate) that the VLOOKUP is expected to do. In this example, we do exact match by passing 0 as the argument for this parameter.
The trick is in using the CHOOSE function to dynamically build the needed lookup range for VLOOKUP. CHOOSE function used in combination with VLOOKUP solves this challenge elegantly.
The combination of CHOOSE with VLOOKUP can be used to solve the challenge of right-to-left VLOOKUP too. Read the article VLOOKUP: Right to Left Lookup to see this interesting scenario and the solution.
Hope that’s a learning for today. Watch the space for more.
Leave a Reply