Volatile formula's, speeding up calculations and match first and last name in separate columns to return value from a third.
Posted on 2016-10-28
First time posting as I'm struggling with a formula that I thought was pretty good and took me awhile to write, as I'm not exactly an expert with Excel. Anyway I'm not ashamed to say I need the help.
The gist of it is simple, the formula is not. Basically I have a list of locker numbers and employees, and other info but I can alter formulas to suit. What I need to do is rather than concatenate the names I want to, in a separate tab, match the surname in column A and the first name in column B to the list of names with locker numbers,surname in A, first name in B, and return column C with the locker number in to the formula cell, whilst leaving this cell blank for missing info in cells containing no names or locker numbers.
They may have several garments and I need to know the locker number on the sheet for each one so the formula is to be dragged down to find the locker for each employee for all their garments, which will be the same locker number for that employee.
The formula I have wrote I believe is classed as a volatile formula and as such is taking far too long to calculate when I amend one of the connected cells. I know you can turn off the calculate options to populate fields but the people who will be using the spreadsheet won't be as clued up on Excel.
Here is the formula currently in place:
=IF(OR(A2=0,B2=0)," ",IF(LOOKUP(2,1/(('Individual PPE'!A:A='Full Wearer and ID Code List'!A2)*('Individual PPE'!B:B='Full Wearer and ID Code List'!B2)),'Individual PPE'!E:E)=0," ",LOOKUP(2,1/(('Individual PPE'!A:A='Full Wearer and ID Code List'!A2)*('Individual PPE'!B:B='Full Wearer and ID Code List'!B2)),'Individual PPE'!E:E)))
It works as intended apart from slowing the sheet down to a halt when amendmending info.
Can someone please advise a way around this or a rejigged formula that will not be volatile and calculate quicker?
Many thanks in advance.