Excel Formula for Inventory

Solution 1:

For future reference, looking up values can be done in several ways (e.g.: VLOOKUPand HLOOKUP), however a very powerful combination can be made using INDEX and MATCH, being at least as fast as other functions and at best much faster.

INDEX

The INDEX function function returns a value or the reference to a value from within a table or range and has got the following parameters:

• An array of cells (matrix) which is required
• An index number of the row we want to return a value from which is required
• An index number of the column we want to return a value from which is optional

So the syntax would look like: =INDEX(RangeOfCells,RowIndex,[ColumnIndex])

MATCH

The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. It has got the following parameters:

• A lookup value which is required
• A lookup array of cells (matrix) which is required
• A match type (-1,0,1) which is optional (automatically type 1 if omitted)

So the syntax would look like: =MATCH(LookupValue,LookupArray,[MatchType])

INDEX + MATCH

Let's imagine a simple dataset in Sheet1 like the following:

| Header1 | Header2 | Header3 | Header4 |
|---------|---------|---------|---------|
| ID1     | A       | Val1    | Month1  |
| ID2     | B       | Val2    | Month2  |
| ID3     | C       | Val3    | Month3  |
| ID4     | D       | Val4    | Month4  |
| ID5     | E       | Val5    | Month5  |

And the following setup to search for a value in Sheet2:

| Header1 | Header2 | Header3 | Header4 |
|---------|---------|---------|---------|
| ID3     |         |         |         |

Now if you are interested in the value of Header2 up to Header4 by looking up a specific value under Header1 you can apply the following technique in B2 and drag right.

=INDEX(Sheet1!$B:$D,MATCH($A2,Sheet1!$A:$A,0),COLUMN(A1))

The result will look like:

| Header1 | Header2 | Header3 | Header4 |
|---------|---------|---------|---------|
| ID3     | C       | Val3    | Month3  |

If you take into consideration the above about INDEX and MATCH you can see that I gave INDEX it's required array of cells (1st parameter), used MATCH to return a row index number (2nd parameter) and have utilized the COLUMN function along with a relative cell reference to return the column index number (3rd parameter).

The MATCH function has been given a lookup value (1st parameter), a lookup array (2nd parameter) and I have used match type 0, which tells the function to look for an exact match.

VLOOKUP

In this specific case it would have been possible to utilize the VLOOKUP function. But I'll try to set out why I would prefer the INDEX + MATCH combination:

• VLOOKUP gives you little flexibility as the lookup value must sit in the left-most column of your lookup matrix, whereas INDEX gives you the option to return any column through the third parameter.
• As previously mentioned, while VLOOKUP is the more popular option, INDEX + MATCH is the faster option. If you go for speed, then use this!

Further notes:

While this was just a simple breakdown of how you could use the combination of INDEX + MATCH, there are a few more things to consider:

• In the example above I used absolute, semi-absolute and relative cell references (note the differences with the $ sign). Utilize this technique to be able to drag a formula to the right, left, bottom or top.
• Set up a table instead of a matrix. You can refer to columns within that table instead of full columns. Searching only a few rows compared to all possible rows in a column is always (AFAIK) faster!
• When you get more advanced you'll notice INDEX and MATCH is something that will appear as a lifesafer much more often in many ways :)
• While looking for an exact match, MATCH might return an error when the lookup value simply isn't found. This is something to consider when you see any #N/A error. A workaround is a ISNA or IFERROR function within the formula.

I hope that gets you started! Check the links I included for some more in-depth information.