VLOOKUP to the Next Level: INDEX and MATCH Functions in Excel

NetSuite ERP has a powerful and robust database structure; it consumes data and outputs data in either it’s the rawest form or organized summaries. NetSuite users are used to massaging and manipulating data. VLOOKUP is a common EXCEL function that is used to map data together. If you want something better but just not at a Dba level, INDEX and MATCH are powerful EXCEL functions.

Use Case for INDEX and MATCH

This function is most common in taking 2 factors to bring back a related value, whereas VLOOKUP only looks at one factor to bring back one related value. A recent customer used this function to help assign transaction type and item to an invoice CSV upload file.

The customer had sales output from another system that they needed to upload them to NetSuite as either Cash Sales or Sales Orders. Originally, the customer was keying in each transaction manually as they did not know how to transform the data so it’s ready for CSV upload. Because there wasn’t a one to one mapping of items, the customer had to use 2 factors to determine if the transaction is a sales order or cash sale and 2 other factors to determine the corresponding NetSuite item. Below walks through how they were able to determine the type of transaction and item in a few min.

How to use INDEX and MATCH to determine the transaction type

In this use case, the customer’s original data set looked like this:

The first step is to build the index. The index is the mapping tool that will be applied to the customer’s data set. In this case, combining the Gift Type and Gift Sub Type to determine what type of NetSuite transaction. This should be saved in a separate tab in the original data set’s EXCEL file; let’s pretend this is Sheet2 (Note: this will be important in the formula below).

Now in the original data set, insert a column and use this formula: =INDEX(Sheet2!$A$1:$D$4,(MATCH(B2,Sheet2!$A$1:$D$1,0),MATCH(C2,Sheet2!$A$1:$A$4,0)))

Copy this formula in column E and based on the Gift Type and Gift Sub Type, the output of the formula will be the NetSuite transaction type.

Here’s a quick explanation of what the formula is doing.

Like longitude and latitude on a map, the formula is using the table that was created in tab: Sheet2 to find a corresponding transaction type.

For instance, Test Doe’s transaction is Gift Type: Corporate and Gift Sub Type: Cash. 

• Looking at the table in Sheet2, we go across to find Corporate and down to find Cash. The cell that intersects determines the transaction type and that value is what is mapped.
• Some quick notes in case you are not familiar with some of the EXCEL notations in the formula
• ‘Sheet2’ refers to the name of the tab I created the INDEX or table in.
• ‘$’ sign indicates locking a cell. What this means, is even if I copy and paste or drag this formula to other cells it will always reference that column or row if it has a $ in front of it. Usually, if you drag a formula down, EXCEL adjusts the cell references.

Hope you enjoyed being a data nerd for a bit. If you need any help importing or exporting or manipulating data, call us and we can help.  

About Us

Concentrus is a complete NetSuite solutions provider that guides organizations through how to use NetSuite to reach highly focused business goals and objectives. We provide NetSuite implementation, developer, integration, and customization services to ensure that you have a long-term solution that is tailored to fit your systems, people, and processes.

Read About Our Implementation Methodology

Want more NetSuite Tips and Tricks? Check out our Short & 'Suite videos