I want to share
a couple of excel tricks for retrieving data from other spreadsheets with
vlookup match and error handling like when reporting #NA. We have a single
excel database that contains all our part numbers, prices, description, vendor
and some more info. What I wanted was to link each member in our ipart table
(eventually a CC item) to our price parts list and ask excel to find our part
number, place it in stock number and find the price and place it in cost
center.
For simplicity I
have copied the price list spreadsheet to a local path next o our library
folder where we have all the components we author. This way I can work on it at
home and in places where the mapped network drive is not accessible. I copy the
new price list each year when it changes. I only used the price in the cost
center of each item to provide a default value instead of leaving it blank.
What we intend
to do is export the BOM and use the same vlookup formula to find the latest
prices before doing a quote. The price
we put in CC is so we can have a value / any value, rather than leaving it
blank bu we intend to update the BOM export with the vlookup formula at every
export.
The tricky part
was finding cells based on partial data. I have the manufacturer code, Georg
Fisher part number, but in our price parts list we don’t have a cell with just
GF Part number instead it holds something like this: “GF REF: 729101108” or “GF
REF: 729101107 DRAIN CENTRE REF. P31127”.
So all I have is 729101108 and I need to use a formula that can find a match
inside a larger text and return the part number and price.
Can’t use left,
right, mid, and len functions because Part Number appears randomly depending on
who entered this info.
The solution is
to use MATCH to find part of a text like *729101108* (* used as wildcard) and
INDEX to get our Part Number value.
Once I found our
part number for that item I can simply use VLOOKUP to get the price since the
part number is a single unique cell value.
Here is the
formula to get our Part Number (we store it in the parts Stock Number
ipropertie).
=IF(ISNA(INDEX('[Price
List.xls]Parts Price
List'!$A$1:$A$15000,MATCH(("*"&U5&"*"),'[Price
List.xls]Parts Price List'!$E:$E,0))),"",INDEX('[Price List.xls]Parts
Price List'!$A$1:$A$15000,MATCH(("*"&U5&"*"),'[Price
List.xls]Parts Price List'!$E:$E,0)))
Sounds complicated?
Let’s brake it into simple terms.
What we’re
saying is IF the value is not found (excel returns NA) then don’t put anything
in the cell (“”)and if found then put the value.
=IF(ISNA(INDEX('[Price List.xls]Parts Price
List'!$A$1:$A$15000,MATCH(("*"&U5&"*"),'[Price
List.xls]Parts Price List'!$E:$E,0))),"",INDEX('[Price List.xls]Parts Price
List'!$A$1:$A$15000,MATCH(("*"&U5&"*"),'[Price
List.xls]Parts Price List'!$E:$E,0)))
MATCH(("*"&U5&"*")
will actually try and find the value in cell U5 (our number 729101108 with
anything before or after it in any part of the text of column E in our price
list. Here is the microsoft help page.
ISNA function on
microsoft’s webpage
Index is used to
retrieve the value of our company’s Part Number (A row) inside our price list. Here
is the microsoft help page.
The price (cost
center) is much simpler.
=IF(M2="","","£"&(VLOOKUP(M2,'[Price
List.xls]Parts Price List'!$A:$F,3,FALSE)))
If there is no
part number (stock number) in cell M then don’t put anything but if there is a
part number then add £ in front and then the value of column 3 in our price
list.
Here is the
VLOOKUP on microsoft webpage.
http://office.microsoft.com/en-gb/excel-help/vlookup-function-HP010343011.aspx?CTT=1
Some
experts say that the IDEX MATCH is more powerfull and faster than VLOOKUP so if
possible use it at all times.
The
best explanation on how to use it and comparasson with VLOOKUP I found here:
and a couple of reasons why VLOOKUP might
not work
I have attached
my Georg Fischer iPart excel template for your review. I am sure it’s better to
see all this at work rather than trying to understand my explanation.
Here is the excel file.
Later,
ADS
No comments:
Post a Comment