Thread: Microsoft Exel - Using match and lookup function

You can search by the invoice number in your inventory list. It involves the use of Excel's "Match" and "Lookup" functions. The" Match" fu nction is used to look for a particular value from a selected cell range. The result is the row in which this value is located. The "Lookup" function can be of two types-vector or array. The vector type of "Lookup" returns a value fram a single row or column range. This is what will be used along with "Match" to give you the results you are looking for.

• Let's consider a small inventory that consists offour columns-"S. No.", "Invoice No", "Batch Quantity" and "Send To". The objective is to search the inventory by the invoice number to get to know, who placed the order and the quantity requested.

• Using the "Match" function will only point to the row in which you will find all the relevant information-here the value will correspond to the "s No" column. This column was deliberately created since
it makes it easier to look for the desired information instead of counting the rows.



• If we are going to enter the "Match" function in cell "H13", then the invoice number you are looking for should be entered in another cell-say, "H12".
The "Match" syntax is, =MATCH(lookup _ value,lookup_
array, match _ type)
"Lookup_value" is the value you are tryingto locate in the inventory list, which is "H12". "Lookup _array" is the range of cells that "Match" needs to look through. Here, it is the "Invoice No" column. Only, select the column that contains the invoice numbers (in this example, it is "C24:C26". "Match_type" can be any of three values-1, 0 or -1. Using "1" in the syntax will return a resultthat is less or equal
to the "Lookup _value". In this case, "Lookup _array" (invoice column) must be sorted in an ascending order. It will have to be sorted out in an ascending order (select the column and click on Edit I Sort).
If "0" is entered, then it looks for the exact value from the "Lookup_array". In this instance, the "Lookup _array" can be unsorted. If "-1" is entered, then it will search for a value that is the smallest and greater than or equal to the "Lookup _value".
• You will be looking for a specific invoice number, hence the "Match type"
is going to be "0". Here is what the formula will look like in cell H13, =MATCH(H12,C4:C26,O)
• Now, enter an invoice number in cell number "H12" and press Enter. Cell number "H13", which is labeled "s No" will display the row that contains the invoice number. Half your worries are solved here. An incorrect value will just display "#NA".
Now that you have found out where your invoice number is, let us fetch the "Batch Quantity" and "Send To" values as well. This way you don't need to scroll through the list. Using the "Lookup" function in this