Use INDEX and MATCH functions for table lookup

The table lists some products and from where they are originally from, and the quantity that my establishment received.

How can we look up the location from where the Product originated from? 

Normally we just create the VLOOKUP function as

“=VLOOKUP("Oranges",$A$2:$C$9,2,0)”

The result would be “Portugal” as we ask to look up “Oranges” within the table and to give the result in column 2.

Nothing wrong with this, BUT, what about if you want to know the reverse, like, from which Location is the product “Oranges”, well here is a bit more tricky as the function VLOOKUP do not look to the left, so using the INDEX AND MATCH function is better and more powerful.

So how will we do it?

For this example we use the syntax:

The MATCH function, syntax:  MATCH(lookup_value;array;match_type)

Lookup_value: is the value you want to match in lookup_array. It can be a value like =MATCH(10,A1:A15,0)or a cell reference like =MATCH(B2,A1:A5,0).
Lookup_array: is a contiguous range of cells containing possible lookup values. Lookup_array must be an array like this: =MATCH(“b”,{“a”,”b”,”c”},0) or an array reference like MATCH(7,A1:A15,0).
Match_type: is the number -1, 0, or 1 as seen here: =MATCH(B2,A1:A5,0). Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.

=INDEX($A$2:$A$9,MATCH("Italy",$B$2:$B$9,0)) = “Tomatoes”, meaning:

• *        INDEX will the range of the result column in the example I want to get the “Product”
• *        MATCH is what you want to look up, in the example is the “Location”, you also need to define the range of the MATCH, the “0” represents EXACT MATCH, so it gives you exactly what you want.

There are loads more of INDEX and MATCH possibilities, I will come back to this subject in the near future.