While searching the web for help with a nested VLOOKUP formula I was attempting, I happened upon a post by Charley at ExcelUser Blog about the INDEX-MATCH function combination that has made working with spreadsheet data a little easier and faster for me. VLOOKUP searches a table for a value in the left-most column and can return a value in the same record from different column to the right. A number of times when using VLOOKUP, I've needed to search for a value in a column other than the left-most column and return a value from a record in a column to the right or left.
For example, the following state abbreviation table can be used with VLOOKUP to return the full state name by looking up the abbreviation, but not vice versa.
| A | B | C | D | |
| 1 | AL | Alabama | 1 | |
| 2 | AK | Alaska | Alabama | |
| 3 | AZ | Arizona | AL |
Of course, I could copy column A and paste into column C and use the table B1:C3 to return the abbreviation by looking up the full state name, but that isn't very clean and becomes impractical with larger tables and more complicated data needs.
INDEX-MATCH uses two functions. The MATCH function returns the record number to the INDEX function, which returns the data actually being sought. The formula in D1 is =MATCH(A1,A1:A3,0). The first argument is the search term, "AL", the next argument specifies the table being searched, and third argument specifies that we want an exact match. The result of the formula is the row number on which the lookup value, "AL", appears.
The formula in D2 utilizes the INDEX function to return the actual value we want, the full state name. The first argument is the table or array, and the second argument is the record number, calculated by the same MATCH function from D1. The formula is =INDEX(B1:B3,MATCH(A1,A1:A3,0)). Note that the two table references need to start and stop on the same cells or else incorrect data could result.
The formula in D2 could just as easily be =VLOOKUP(A1,A1:B3,2,FALSE). However, INDEX-MATCH does not require the lookup value to be in the left most column. The formula in D3 looks up the value in B1 and returns the corresponding abbreviation: =INDEX(A1:A3,MATCH(B1,B1:B3,0)). VLOOKUP is unable to accomplish this with the current table.
These same principles apply with HLOOKUP, which is is the same as VLOOKUP except it utilizes a table turned on its side. INDEX-MATCH is more flexible and it apparently faster as well.
0 comments:
Post a Comment