xLookup

Quick example of how to use xLookup.

Get prices for items:

In the below example, I want to use xLookup to return the prices for my items.

lookup_value: I want this to be the item name.

lookup_array: This should be the list of items in the prices table.

return_array: This is the list of prices so that when and item matches something in the lookup_array, this will return the price from that matching row.

The key to getting this to work for all the items is to lock the references for the lookup_array and return_array. This means as we fill down the column, the lookup_value will change, but the arrays will always point to the pricing table.

The fast way to lock the references is to put your edit cursor in the reference for the formula and press F4. This will add the $, so E1 will turn into $E$1.