I'm trying to find a way to speed up this calculation.
=IF(ISERROR(INDEX('Current BOM''s'!E:E,MATCH(1,('Current BOM''s'!C:C=E13)*('Current BOM''s'!D:D="TOTAL"),0),1)),VLOOKUP(E13,'Current BOM''s'!C:E,3,FALSE),IF(ISBLANK(E13),"",INDEX('Current BOM''s'!E:E,MATCH(1,('Current BOM''s'!C:C=E13)*('Current BOM''s'!D:D="TOTAL"),0),1)))
It works for all my conditions, but it takes a bit. The INDEX part of the function I came up with is what is confusing the most to me. From the examples I looked up, it would be the column where the data I'm trying to return is stored...I'm just not sure that's correct.
E13 is the number I'm looking for on the Current BOM's worksheet, for instance 7719218976. I'm putting my formula in cell G13.
If E13 is blank then the cell value should be "". The Current BOM's worksheet could have multiple occurrences of the value in E13 in column C. It looks for E13 in column C, then looks for the row where column D = "TOTAL", and returns the value for column E. If it can't find a match where TOTAL is in column D, then it just runs a VLOOKUP on E13 and still returns column E. The reason I'm having to do this is because some numbers I'm looking up are components and others are assemblies where the value I need is a total.
Another way to say what I'm trying to do is:
I want to search for E13 in column C of worksheet "Current BOM's". I'm wanting to return the value of column E. However, if there are multiple occurrences of E13, I want the one where column D = "TOTAL". If E13 is blank, I want the value to show as "".
Sometimes it's easier for me to say what I'm trying to make the formula do that to write it.
Bookmarks